Skip to content

DATETIME and its surprises

by on October 22, 2014

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 timedatedatetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. timedatetime2 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:

12

112

ISO

12 = yymmdd

112 = yyyymmdd

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.

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: