Be Wary of Date Formatting in T-SQL

Today’s public service announcement is a reminder to be wary of date formatting in SQL Server.

On a recent mailing list discussion, one person demonstrated the following code (which I’ve adapted for this post):

SET LANGUAGE 'us_english';
GO
DECLARE @StartDate DATETIME = '2017-07-12';
SELECT @StartDate;
GO
DECLARE @StartDate DATE = '2017-07-12';
SELECT @StartDate;
GO
DECLARE @StartDate SMALLDATETIME = '2017-07-12';
SELECT @StartDate;
GO
DECLARE @StartDate DATETIME2 = '2017-07-12';
SELECT @StartDate;
GO

As you might expect, every single SELECT returned a date of 12 July 2017.

Now this is where it gets interesting. Change the language from US English to British English:

SET LANGUAGE 'British';
GO
DECLARE @StartDate DATETIME = '2017-07-12';
SELECT @StartDate;
GO
DECLARE @StartDate DATE = '2017-07-12';
SELECT @StartDate;
GO
DECLARE @StartDate SMALLDATETIME = '2017-07-12';
SELECT @StartDate;
GO
DECLARE @StartDate DATETIME2 = '2017-07-12';
SELECT @StartDate;
GO

Look carefully. DATE and DATETIME2 are showing the date of 12 July 2017 as expected. Unfortunately, the DATETIME and SMALLDATETIME data types are showing a date of 7 December 2017.

That’s not good at all. It means that the ISO 8601 standard does not work the way we might expect it to. The reason is simple, if annoying: we need to add a time to the date to make it pass the ISO 8601 standard.

Microsoft’s own documentation is very clear about this (emphasis added):

The ISO 8601 date with time format is as follows:

• YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
• YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Coordinated Universal Time)

To use the ISO 8601 format, you must specify each element in the format. This includes the T, the colons (:), the + or – , and the periods (.). The brackets indicate that the fractional seconds or time zone offset components are optional.

In other words, if we want to use a hyphen separator in our date strings, we need to include a time as well.

SET LANGUAGE 'British';
GO
DECLARE @StartDate DATETIME = '2017-07-12T00:00:00';
SELECT @StartDate;
GO
DECLARE @StartDate DATE = '2017-07-12T00:00:00';
SELECT @StartDate;
GO
DECLARE @StartDate SMALLDATETIME = '2017-07-12T00:00:00';
SELECT @StartDate;
GO
DECLARE @StartDate DATETIME2 = '2017-07-12T00:00:00';
SELECT @StartDate;
GO

We could also use a different date format. Instead of separating the year, month and day with a hyphen, use YYYYMMDD format instead. This is considered unambiguous, if a little trickier to read.

The following code works irrespective of the LANGUAGE setting.

DECLARE @StartDate DATETIME = '20170712';
SELECT @StartDate;
GO
DECLARE @StartDate DATE = '20170712';
SELECT @StartDate;
GO
DECLARE @StartDate SMALLDATETIME = '20170712';
SELECT @StartDate;
GO
DECLARE @StartDate DATETIME2 = '20170712';
SELECT @StartDate;
GO

Moral of the story: don’t make assumptions.

If you want to explode your brain on dates and times in SQL Server, check out Tibor Karaszi’s ultimate guide to the datetime data types.

To share more language compatibility weirdness, find me on Twitter at @bornsql.