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 datatypes.

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

Author: randolph

Randolph West is a Microsoft Data Platform MVP, and has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen, or doing voices for independent video games. Randolph is available for talks on SQL Server, and technology in general. He also offers training for junior DBAs. Connect with Randolph on Google+ or Twitter.

2 thoughts on “Be Wary of Date Formatting in T-SQL”

  1. The moral is don’t use DateTime and SmallDateTime as they are old date formats.
    The problem is, for the old date types, when SQL Server does not know what to do it always assumes the date is in the US format.
    This is a crazy assumption as the only country in the world that uses that strange middle-endian format is the US.
    With the new DateTime2 and Date formats this is no longer the case as they assume ISO 8601.

  2. Or, if you are running SQL Server 2012 or newer, use the appropriate function:

    datetimefromparts
    datefromparts
    smalldatetimefromparts
    datetime2fromparts

Comments are closed.