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://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

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?

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

Leave a Reply

Your email address will not be published.