Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

SQL Server: The one and only locale insensitive date format

Until recently i've been sure that there are 2 date formats that are completly locale insensitive.

Those 2 i thought were locale insensitive are yyyy-mm-dd HH:mm:ss.fff and yyyymmdd HH:mm:ss.fff.

And i was proven wrong a few days ago!

 

The only locale insensitive date format is yyyymmdd HH:mm:ss.fff

you can easily test this with this script:

 

DECLARE @t1 table (date DATETIME)

SET DATEFORMAT ymd INSERT INTO @t1 ( date ) VALUES ( '2007-10-22 10:15:3.83' )

– this fails SET DATEFORMAT ymd INSERT INTO @t1 ( date ) VALUES ( '2007-22-10 10:15:3.83' )

SET DATEFORMAT ymd INSERT INTO @t1 ( date ) VALUES ( '20071022 10:15:3.83' )

SET DATEFORMAT ydm INSERT INTO @t1 ( date ) VALUES ( '20071022 10:15:3.83' )

SELECT * FROM @t1

 

Of course this is irrelevant with proper use of parameters, but that's not the point of the post :)

 

kick it on DotNetKicks.com

Legacy Comments


georgev
2007-10-22
re: SQL Server: The one and only locale insensitive date format
Surely this is going to fail... Look at the date format!!

-- this fails
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '2007-22-10 10:15:3.83' )

Typo?

Mladen
2007-10-22
re: SQL Server: The one and only locale insensitive date format
this is going to fail, yes. as is said in the comment.
that's the point.
or am i misunderstanding you?

georgev
2007-10-22
re: SQL Server: The one and only locale insensitive date format
Just for clarification run this

DECLARE @t1 table (date DATETIME)

-- this fails (because there are only 12 months and not 22!)
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '2007-22-10 10:15:3.83' )

-- Note the change in dateformat?
SET DATEFORMAT ydm
INSERT INTO @t1 ( date )
VALUES ( '2007-22-10 10:15:3.83' )


SELECT *
FROM @t1

Mladen
2007-10-22
re: SQL Server: The one and only locale insensitive date format
yes the whole point of this was that it fails and so it's not a format independant format.
only yyyymmdd is.

ML
2007-10-22
re: SQL Server: The one and only locale insensitive date format
Actually, there is another unambiguous method of representing date/time values using characters:

yyyy-mm-ddThh:mm:ss.ttt

This is the combined date/time character representation format; it's part of the ISO 8601 standard.

Here's more info:
http://en.wikipedia.org/wiki/Iso_date


ML

Mladen
2007-10-22
re: SQL Server: The one and only locale insensitive date format
ewww.... that's used in xml. we don't like xml, do we? :))

acctually i know about this but i've excluded it on purpose since this fails:

INSERT INTO @t1 ( date )
VALUES ( '2007-10-22T10:15:3.83Z' )

it isn't as robust as the yyyymmdd ....

ML
2007-10-22
re: SQL Server: The one and only locale insensitive date format
What's wrong with XML? The XML standard implements the ISO Date standard, and SQL implements both. Not a full 100% at the moment, but it's there. :)

Also, be careful with the length of the elements: seconds take up *two* places, and thousands take up *three*.

select cast('2007-10-22T10:15:03.830Z' as datetime)

If you play nice, SQL plays nice. ;)


ML

Mladen
2007-10-22
re: SQL Server: The one and only locale insensitive date format
i'm kidding! :)) i like xml just fine!

exactly my point about the element length.
the Z notation (as i call it ) doesn't handle them, while the original yyyymmdd HH... does.

it more robust, that's all.

ML
2007-10-22
re: SQL Server: The one and only locale insensitive date format
Z stands for Zulu. :) Zero-point. Greenwich Mean Time (GMT).

I suggest you read that Wikipedia article - I have a feeling you haven't done so yet. Apologies if you have.

If by "robust" you mean "fool-proof" then I agree. On the other hand, fools have no business in software development, if you know what I mean. :)


ML

Mladen
2007-10-22
re: SQL Server: The one and only locale insensitive date format
oh come on... Z notation sounds so much better :)
I know all about GMT, UTC and ZULU. too much JAG... ;)

i agree that fools have no business in software development but you know that there's no such thing as fool-proof or development without fools. we all were a fool at one time, no?

by robust i mean harder to break. nothing is fool-proof :)

Chris
2007-10-23
re: SQL Server: The one and only locale insensitive date format
I wouldnt class 2007-22-10 as a valid date, regardless of locale.

The idea of yyymmdd (ISO date format) is that it's most significant to least significant.

It gets past the dd-mm / mm-dd differences as there is only 1 way for it, not two and thats the purpose of putting it in that order.

Mladen
2007-10-23
re: SQL Server: The one and only locale insensitive date format
good point chris.