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.

Data Efficiency in SQL Server: DATETIME

This is the first in a short series of posts about how I assign efficient data types when designing a new table or database.

Use less space with DATETIME2

We all know that the DATETIME column uses 8 bytes of space to store the date and time, to an accuracy of a paltry 3 milliseconds.

This used to cause no end of drama in the days before SQL Server 2008 because milliseconds were rounded to end in 0, 3 or 7. When trying to calculate the last possible time in a day, you had to work with some crazy values like 2016-03-15 23:59:59.997.

Fortunately, SQL Server 2008 introduced DATETIME2, which put paid to that drama. We now have a precision of up to 7 places after the decimal, and it still only uses a maximum of 8 bytes!

For example, we can now store a value of 2015-03-15 23:59:59.9999999 which is mind-bogglingly close to midnight, and not worry about insane values ending in a 7.

Do you really need that precision, though? How about a way to actually use less storage and store more accurate data?

If 0–2 places of precision (after the decimal) requires only 6 bytes, 3–4 places requires 7 bytes, and 5–7 places requires the full 8 bytes, we can save ourselves a whole byte and not lose any precision by using DATETIME2(3) as a drop-in replacement for DATETIME.

We get precision down to the millisecond (even more accurate than before) and shave off one byte for every row in that column. In a table with ten million rows, that’s 9.5MB of space we no longer have to store, or query, or index, or back up.

What data efficiency method have you used in production? Let me know on Twitter at @bornsql.