Working with Date and Time Formats in T-SQL

Introduction

The purpose of this article is to deal with formatting problems that may occur when working with time and date and their solutions. As we know, T-SQL has the following types for working with time and date:

Date
Time
Smalldatetime
Datetime
Datetime2
Datetimeoffset

Recommended among these types are date, datetime2, and datetimeoffset. The smalldatetime and datetime types are both non-compliant with the SQL standard and allow some degree of rounding.

SELECT CAST (GETDATE() as datetime) as [datetime], 
CAST(GETDATE() as datetime2) as[datetime2];

Output

SET dateformat 'ymd';

DECLARE @date as varchar(40)='2011-12-24 23:59:59.998';
SELECT CAST(@date as datetime) as [datetime],
    CAST(@date as datetime2) as [datetime2]

Output

The main thing to pay attention to when working with time and date is the formatting rules and the choice of appropriate language.

In T-SQL, 2 main commands are used to see the current date format:

DBCC UserOptions

SELECT TOP(1) date_format FROM sys.dm_exec_sessions

Output

Date format can be given in different forms: 'Ymd', 'ydm', 'myd', 'mdy', 'dmy', 'dym'

The recommended format for formatting the time and date is 'yyyyMMdd'. For example, 20221224, 20230428, etc

However, it should be taken into account that the order of time and date determination is different in different countries (Depending on culture). So, some countries may have combinations of dd-MM-yyyy, others yyyy-MM-dd, MM-dd-yyyy, etc.

'yyyyMMdd' always works for all languages, no matter what language configuration T-SQL is in!

When formatting time and date, the most commonly used formatting symbols are:

"-": (Hyphen or Dash)
".": (Period or Dot)
"/": (Slash or Forward slash)

Example

yyyyMMdd
yyyy-mm-dd
yyyy.mm.dd
yyyy/mm/dd

We need 2 basic configurations to experiment with which date format is correct and in which cases.

One is the language, and the other is the format rule. To know this, you need to write DBCC useroptions and look at the language and date format values.

If we want to change each of these values, SET LANGUAGE and SET DATEFORMAT commands are used.

Our tests will be based on language = us_english.

Before starting the tests, let's divide our types into 2 groups:

Unstable datetime and smalldatetime, which allow rounding. Of these types, datetime will be involved.
Datetime2 and datetimeoffset are considered stable to allow rounding. Of these types, datetime2 will be involved.

TEST 1. 'yyyyMMdd' format works with all date types regardless of DATE FORMAT.

SET LANGUAGE 'us_english'
SET DATEFORMAT 'ymd';

--ydm = Year Month Day
--below format is working without format dependency.
--It means it is valid for 'ymd' , 'ydm' , 'myd' , 'mdy' , 'dmy' , 'dym'

DECLARE @defactoWorkingDate as varchar(10) = '20220225';
SELECT CAST(@defactoWorkingDate as datetime2) as [datetime2]
SELECT CAST(@defactoWorkingDate as datetime) as [datetime]

TEST 2. datetime2, datetimeoffset, and date data with a slash, Dash, and hyphen style work regardless of date format ( SET DATEFORMAT )

SET DATEFORMAT 'ydm';--'ymd','ydm','myd','mdy','dmy','dym' FOR ALL

--below styles work without date format dependency.
--It means they are valid for 'ymd','ydm','myd','mdy','dmy','dym'

DECLARE @defactoWorkingDateWithdot as varchar(10) = '2022.02.25';
DECLARE @defactoWorkingDateWithDash as varchar(10) = '2022-02-25';
DECLARE @defactoWorkingDateWithSlash as varchar(10) = '2022/02/25';

SELECT CAST(@defactoWorkingDateWithdot as datetime2) as [datetime2]
SELECT CAST(@defactoWorkingDateWithDash as datetime2) as [datetime2]
SELECT CAST(@defactoWorkingDateWithSlash as datetime2) as [datetime2]

TEST 3. All date formats work, without exception if they match the specified DATE FORMAT.

SET DATEFORMAT 'dmy';

DECLARE @defactoWorkingDate as varchar(10)='25-10-2023';

SELECT CAST(@defactoWorkingDate as datetime2) as [datetime2]
SELECT CAST(@defactoWorkingDate as datetime) as [datetime]

TEST 4. 'ydm' format gives an error for date, datetime2, and datetimeoffset in some cases.

SET DATEFORMAT 'ydm';

--YMD format is not supported

DECLARE @defactoWorkingDate as varchar(10) = '23/25/12';


SELECT CAST(@defactoWorkingDate as date) as [datetime2]
SELECT CAST(@defactoWorkingDate as datetime) as [datetime]

TEST 5. Although the 'yyyy-mm-dd 'style works for a date, datetime2, and datetimeoffset regardless of the format, the style must be in the specified format for smalldatetime and datetime.

SET DATEFORMAT 'ydm';

--it is working for datetime2, date and datetimeoffset
--but fails for datetime and soalldatetime
--for smalldatetime and datetime, style should be same as DATE FORMAT

DECLARE @date as varchar(10)='2023-10-19';

SELECT CAST(@date as date) as [datetime2]
SELECT CAST(@date as datetime) as [datetime]

TEST 6. 'mdy' and 'myd' formats work with all date types for yyyy-mm-dd.

SET DATEFORMAT 'myd'; --mdy also ok

DECLARE @date as varchar(10) = '2023-10-19' ;

SELECT CAST(@date as date) as [datetime2]
SELECT CAST(@date as datetime) as [datetime]

TEST 7. 'dmy' and 'dym' formats give errors in the 'yyyy-MM-dd' format for smalldatetime and datetime types, but work for datetimeoffset, date, and datetime2.

SET DATEFORMAT 'dmy'; --dym also fails for datetime, but works for date/datetime2

DECLARE @date as varchar(10) = '2023-10-19';

SELECT CAST(@date as date) as [datetime2]
SELECT CAST(@date as datetime) as [datetime]