THE SQL Server Blog Spot on the Web

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

Tibor Karaszi

Is 'YYYY-MM-DD' always treated as year, month, date?

Most of you already know the answer to this question: no.

I won't recap the rules for interpretation of the current datetime literals, as I have an article on the subject: http://www.karaszi.com/SQLServer/info_datetime.asp

However, SQL Server 2008 introduces a number of new date, datetime and time datatypes. One interesting aspect of these is that interpretation of the ANSI SQL datetime format, 'YYYY-MM-DD' is independent of language and datetime settings.

SQL Server MVP Steve Kass opened up the discussion (along with a connect suggestion) that it is time for the old datetime datatype also always treat this format as year, month, date. In general, I'm all for a consistent treatment of this datetime formatting, and that should indeed be the most "natural" one and also adhere to ANSI SQL. However, I don't feel qualified to estimate if or how much backwards compatibility problems this will cause. Are any of you using format 'YYYY-DD-MM' format, for instance?

I suggest you check out Steve's connect entry at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=290971 and make your voice heard.

Here's a script that show you whether or not SQL Server treats 'nnnn-nn-nn' as 'yyyy-mm-dd' or something else. For fun, if you have July CTP of SQL Server 2008, you can run the same script and change the datatypes to datetime2 and compare the difference.

SET NOCOUNT ON
USE tempdb
IF OBJECT_ID('langdf') IS NOT NULL DROP TABLE langdf
CREATE TABLE langdf(lang sysname, dt datetime, correct bit)

DECLARE @lang sysname, @sql nvarchar(4000)
DECLARE c CURSOR FOR SELECT alias FROM master.dbo.syslanguages
OPEN c
WHILE 1 = 1
BEGIN
FETCH NEXT FROM c INTO @lang
IF @@FETCH_STATUS <> 0 BREAK
SET @sql =
 'SET LANGUAGE "' +
 @lang + '"
  INSERT INTO langdf
  SELECT
    ''' + @lang + '''
    ,CAST(''2007-02-09'' AS datetime)
    ,CASE WHEN CAST(''2007-02-09'' AS datetime) = ''20070209'' THEN 1 ELSE 0 END'
PRINT @sql
EXEC(@sql)
END
DEALLOCATE c

SELECT
 COUNT(NULLIF(correct, 0)) AS "correct interpretation"
,COUNT(NULLIF(correct, 1)) AS "incorrect interpretation"
FROM langdf

SELECT * FROM langdf

Published Saturday, August 04, 2007 12:06 PM by TiborKaraszi
Filed under: ,

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

 

Adam Machanic said:

I am personally all for standardization on this issue.  I wasn't aware that this was defined per ANSI SQL, but given that the YYYY-MM-DD format is ISO 8601 compliant--a much more far reaching standard than that for SQL--I think it's certainly a good move.

I guess it should be easy enough for people to tell if they'll have problems with this change, if they know to look for it.  I don't know how many locales use YYYY-DD-MM, but I have not seen it often (if ever?) on forums/newsgroups/etc, so I think it's probably not at all common...

August 4, 2007 10:12 AM
 

TiborKaraszi said:

I just voted 5 on Steve's connect entry.

In all fairness yyyymmdd is consistent with ISO 8601, and so is some other less frequently used formats. To me, the important aspects are

* ANSI SQL compliance

* this feels natural to most people (going outside your cultures representation) as "the international" representation

* compatibility with the new date and datetime datatypes

* compatibility with the rest of the world

Hopefully some pre-upgrade tool (I'm thinking something using SQL trace) could be shipped that can run and catch dash-separated datetime literals and warn if value is outside the range (2007-28-02). However, I don't think I've ever seen such a datetime representation...

August 4, 2007 12:46 PM
 

Edward Benson said:

On the SQLblog front page, the date of articles is formatted MM-DD-YYYY

ISO 8601... PAH!

Perhaps you could lead the way and format all dates on here consistently YYYY-MM-DD

August 23, 2007 8:29 AM

Leave a Comment

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