THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Unambiguous date formats : T-SQL Tuesday #001

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this:

DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00';

SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing:

SELECT @@LANGUAGE

To demonstrate how your language setting can influence the results of a cast take a look at the following code:

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005
USE tempdb
GO
DECLARE @t TABLE (
     
dateString  NVARCHAR(19)
);
INSERT @t (dateString)
VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss'
,      ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss'
,      ('20091208 18:00:00')   --'yyyyMMdd hh24:mi:ss'

SET        LANGUAGE french;
SELECT 'french' AS lang
,      DATENAME(MONTH,q.[dt]) AS mnth
,      q.[dt]
FROM   (
      
SELECT  CAST(dateString AS DATETIME) AS dt
      
FROM    @t
      
)q;
SET        LANGUAGE us_english;
SELECT 'us_english' AS lang
,      DATENAME(MONTH,q.[dt]) AS mnth
,      q.[dt]
FROM   (
      
SELECT  CAST(dateString AS DATETIME) AS dt
      
FROM    @t
      
)q;

We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then seeing how the @@LANGUAGE setting can affect the results. Here are those results:

french language datetime

Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8th December 2009.

Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats:

  • yyyy-MM-ddTHH24:mi:ss
  • yyyyMMdd HH24:mi:ss

That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you):

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008
GO
USE tempdb
GO
SET        LANGUAGE french;
DECLARE    @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format
SELECT CAST(@dt AS datetime) AS [ExplicitCast]
,      DATENAME(MONTH,@dt) AS [MonthFromImplicitCast]
,      DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast];

Here we are doing three different things with our nchar literal:

  • explicitly cast it as a [datetime]
  • extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast)
  • extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime]

Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results:

image

(Were you correct?)

Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that?

To get the answer we can turn to BOL’s description of the DATENAME function syntax:

image

The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better.

These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats:

  • yyyy-MM-ddTHH24:mi:ss
  • yyyyMMdd HH24:mi:ss

regardless of which version you are on!

Don’t forget to check out other T-SQL Tuesday blog posts, see Adam Machanic’s blog post Invitation to Participate in T-SQL Tuesday #001: Date/Time Tricks for details!

@Jamiet

Published Tuesday, December 08, 2009 9:23 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Todd McDermid said:

TYVM for the research, Jamie.  Off to amend a blog post or two to shove "T" in between the date and time in the purported "unambiguous" date/time format...

BTW - What's the unambiguous format for dates without time?  "YYYY-MM-DDT"?

December 8, 2009 4:48 PM
 

Barry said:

Isnt it doing this because you are storing it as NCHAR(10) , so it isnt keeping the time element at all..

December 9, 2009 6:37 AM
 

Tom Winter said:

Thanks! Great post.

December 9, 2009 8:34 AM
 

TiborKaraszi said:

Todd,

<<BTW - What's the unambiguous format for dates without time?  "YYYY-MM-DDT"?>>

Converted to what type? As long as it is a string, then it isn't a date/time/datetime (yet). So, to be able to answer above, we need to know into what type the value will be cast:

datetime? There's no datetime value without time. That is impossible. You can have a string representation without time, which will be cast to datetime and have time value of 00:00:00.000. Here are two language independent such formats:

'20081223'

'2008-12-23T00:00:00' (yes, t he time portion is needed for ISO 8601 format)

date, datetime2 and other new date-related types? Above are language independent for new date types as well, but also below format:

'2008-12-23'

(http://www.karaszi.com/SQLServer/info_datetime.asp )

December 9, 2009 10:32 AM
 

Rhys said:

I think loads of developer time would have been saved over the years if the world had done the sensible thing and agreed upon a single date format.

December 15, 2009 12:56 PM
 

SSIS Junkie said:

One requirement in any database implementation that I have ever worked on is that the notion of a point-in-time

December 28, 2009 6:25 PM
 

Geoff Clark said:

If date is sufficient for your constants (not time) you can use the ODBC specific date format in Transact-SQL and this is unambiguous for Language.

{ d 'yyyy-mm-dd' }

e.g.

DECLARE @lMAXDate datetime

SET @lMAXDate = {d '9999-01-01'}

select @lMAXDate

January 21, 2010 6:24 PM
 

Geoff Clark said:

Comment to added signin to comment above

January 23, 2010 9:17 AM
 

Robert Heinig said:

yyyy-MM-ddTHH:mm:ss (.net lingo) is the ONLY literal format reliably interpreted correctly across types, versions and context. Yes, T00:00:00 for target type date. Where date/string conversion cannot be avoided in an SSIS package, for direction ->string use:

String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0:o}", DateTime.Now).Substring(0,19)

Using SSIS CAST for the parse direction is just inviting trouble. I found it pays to use DateTimeOffset.TryParseExact and an explicit list of allowed formats (in my template that's 18 strings).

May 3, 2013 10:10 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement