November 24, 2024
Headlines

Working with T-SQL Date and Time Formats

Craig Johnson08 mins

This article addresses formatting issues that may arise when working with time and date, as well as their solutions. T-SQL has the following categories for working with time and date, as is well known:

Date Time Smalldatetime
DateTimeDatetime2 Datetimeoffset

Among these kinds, date, datetime2, and datetimeoffset are recommended. Both the smalldatetime and datetime data formats are non-compliant with the SQL standard and permit rounding to some degree.

SELECT CAST (GETDATE() as datetime) as [datetime],
CAST(GETDATE() as datetime2) as[datetime2]; 
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]

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

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:

  1. Unstable datetime and smalldatetime, which allow rounding. Of these types, datetime will be involved.
  2. 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]
SQL

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]

Related News