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 :)
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. |