SSIS Junkie : datetime

Standard Point-in-time and time-interval representations

jamiet — Mon, 28 Dec 2009 23:25:16 GMT

One requirement in any database implementation that I have ever worked on is that the notion of a point-in-time has to be represented in some way. The tool that I use on a regular basis, SQL Server, provides numerous datatypes that aid in the representation of a point-in-time and I’m sure that most people reading this will be au fait with them. They are:

I suspect (though stand to be corrected) that the accuracy with which people wish to record a point-in-time is usually to the nearest day or the nearest second however until very recently neither of these accuracies was specifically catered for with a dedicated datatype. Up until the introduction of the date datatype in SQL Server 2008 datetime was the datatype that most would use to record a point-in-time however the accuracy of datetime is neither day nor second, it is 0.00333 seconds; smalldatetime could be used but that also does not have accuracy to the day or second, it is to the minute.

The point I’m trying to make is not that people are using the wrong datatypes to store their data, nor that everyone should just use the most accurate point-in-time datatype (that being datetime2 which is accurate to 100 nanoseconds). No, my point is that there are many different situations which have a need to store a point-in-time and SQL Server does not provide datatypes to cater for all of them. That is what prompted me to raise a Connect submission entitled [T-SQL] Month & Quarter datatype where I asked for datatypes that represent a Month or a Quarter. Simon Sabin commented that he also wanted datatypes to represent a time interval (and Aaron Bertrand pointed out that a Connect submission already exists for that) whilst Rob Farley ran with my idea and suggested that we should have week, hour, semester datatypes as well. There are some good justifications on the submission for why such datatypes are required so if I have failed to convince you here feel free to click through and take a read.

The point of this blog post though is not to drum up support for my Connect submission (though that would be nice), instead it is to make reference to the reply I received from a gentleman on the SQL Server team going by the name ‘Jim’. Jim pointed me to an article on Wikipedia which talks about the ISO 8601 standard that provides an agreed standard for the representation and exchange of date and time related data.

I previously assumed that the only standard date/time formats were something like YYYYMMDD & YYYYMMDDTHH24:MI:SS but not so, ISO 8601 provides representations for centuries, years, months, weeks, ordinal dates, hours, minutes, seconds, fractions of time units, timezones and time intervals (but not quarter, unfortunately). I won’t go into much detail about the standard as you can go and read about it for yourself (and I recommend the wikipedia article as essential reading for any data professional, particularly the General Principles) but for the benefit of both myself and others I have provided below a summary of all the point-in-time representations that the wikipedia article mentions; and fascinating it is too (to me anyway). Take the time to have a read, you may be enlightened:

Valid unambiguous representation	Description
21	21st Century
1981	Year 1981
2004-05 (but not 200405)	5th month of the year 2004
20090106 2009-01-06	6th day of the 1st month of the year 2009
2006W34 2006-W34	34th week of the year 2006
2006W343 2006-W34-3	3rd day of the 34th week of the year 2006
1981095 1981-095	95th day of the year 1981 (better known as 1981-04-05)
21	21st hour of a day
2135 21:35	35th minute of the 21st hour of a day
213507 21:35:07	7th second of the 35th minute of the 21st hour of a day
0000 00:00 2400 24:00	Midnight
1430.5 14:30.5	14 hours, 30 minutes and one half minutes
143050.25 14:30:50.25	14 hours, 30 minutes, 50 seconds and one quarter second
0930Z 09:30Z	9 hours and 30 minutes coordinated universal time (aka UTC time aka Zulu time)
+0100 +01:00	1 hour ahead of UTC
1830Z 22:30+04 1130-0700 15:00-03:30	18 hours and 30 minutes UTC (the point being that these all represent the same point-in-time)
2007-04-05T14:30	30th minute of the 14th hour of the 5th day of the 4th month of the year 2007 (“T” is the standard separator between date and time)
2007-04-05T24:00 2007-04-06T00:00	Midnight inbetween the 5th & 6th days of the 4th month of the year 2007

Some things to note:

Some representations are overloaded (e.g. 21 can mean the 21st century or 21st hour of the day) so context can be a factor.
The week notation is still prone to ambiguities; when does week 1 start? More pertinently, when does week 2 start? Apparently the ISO defines a standard for defining this but it isn’t adhered to as stringently as ISO 8601.
The week notation can also be quite confusing, if the ISO directives are followed to the letter then “2009-W53-7” represents the 3rd day of the first month of 2010 (read the wikipedia article to understand why).

The article also provides some standard representations of durations:

Valid unambiguous representation	Description
P3Y	A period of 3 years
P6M	A period of 6 months
P4D	A period of 4 days
P12H	A period of 12 hours
PT6M	A period of 6 minutes (note the T designation)
P5S	A period of 5 seconds
P3Y6M4DT12H30M5S	A period of three years, six months, four days, twelve hours, thirty minutes, and five seconds
P23DT23H	A period of 23 days and 23 hours

and time intervals:

Valid unambiguous representation	Description
2007-03-01T00:00:00Z/2008-05-11T15:30:00Z	The interval between: midnight (UTC) at the start of the first day of the 3rd month of the year 2007 the 30th minute of the 15th hour (UTC) of the 11th day of the 5th month of the year 2008
2007-03-01T13:00:00Z/P1Y2M10DT2H30M	The interval between: the 13th hour (UTC) of the first day of the 3rd month of the year 2007 the point-in-time exactly 1 year, 2 months, 10 days, 2 hours and 30 minutes later

One last interesting point of trivia, the second edition of ISO 8601 (published in 2000) allowed for 2-digit dates whereas the third (and at the time of writing, current) version (published in 2004) does not. Hmmm… did someone say Y2k?

OK, time to wrap up; why am I writing this? Well, unambiguous representations of data are of paramount importance especially in the distributed heterogeneous XML/JSONified world of data exchange that we live in today so a good understanding of the standards that define those representations should be a pre-requisite to working in that arena. I hope this blog post has highlighted the fact that these standards exist and that you feel compelled to adhere to them in your future work. If you want to understand how damaging it can be to veer from the unambiguous formats then take a read of my recent blog post Unambiguous date formats.

Oh, and if you want to vote for a month datatype in SQL Server, be my guest :)

@Jamiet

Unambiguous date formats : T-SQL Tuesday #001

jamiet — Tue, 08 Dec 2009 20:23:41 GMT

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:

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 12^th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8^th 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:

(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:

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