DATETIME and its surprises

Well, it’s about time I write another blog post (with a promise that I’ll do it more than once a year).

I’m going to start a series of “bad habits to kick” as for me it is important that we all have a common ground on SQL Programming.

1.            Consider the following scenario:

SET

LANGUAGE British;

SELECT
CAST(’02/12/2007′
AS
DATETIME);

SET
LANGUAGE us_english;

SELECT
CAST(’02/12/2007′
AS
DATETIME);

SET
LANGUAGE British;

SELECT
CAST(‘2007-02-12’
AS
DATETIME);

SET
LANGUAGE us_english;

SELECT
CAST(‘2007-02-12’
AS
DATETIME);

SET
LANGUAGE British;

SELECT
CAST(‘20070212’
AS
DATETIME);

SET
LANGUAGE us_english;

SELECT
CAST(‘20070212’
AS
DATETIME);

And let’s see the results:

Changed language setting to British.

———————–

2007-12-02 00:00:00.000

(1 row(s) affected)

Changed language setting to us_english.

———————–

2007-02-12 00:00:00.000

(1 row(s) affected)

Changed language setting to British.

———————–

2007-12-02 00:00:00.000

(1 row(s) affected)

Changed language setting to us_english.

———————–

2007-02-12 00:00:00.000

(1 row(s) affected)

Changed language setting to British.

———————–

2007-02-12 00:00:00.000

(1 row(s) affected)

Changed language setting to us_english.

———————–

2007-02-12 00:00:00.000

(1 row(s) affected)

So the first two results are different, while the last query returns the same result! What’s going on?

It has to do with the way Microsoft implemented the date and time data types, starting with SQL Server 2008.

We have two similar data types, DATETIME and DATETIME2. What people fail to realize is that the two data types behave completely differently (Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications – see here.)

DATETIME preserves the old, non-standard ANSI  settings, in order to be backwards compatible, while DATETIME2 is pretty much standard compatible.

2.           How can we address this issue?

The answer lies in the code itself (repeated here for clarity):

SET
LANGUAGE British;

SELECT
CAST(‘20070212’
AS
DATETIME);

SET
LANGUAGE us_english;

SELECT
CAST(‘20070212’
AS
DATETIME);

This will return the same date and time; and the reason lies (again) in Books Online, for CAST and CONVERT:

For date and time styles, we have:

In other words, even if DATETIME is not ISO standard, the way we input data for CAST can be aligned with the ISO standard, thus having the correct results each time.

I hope this helps.