I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

Print | posted on Monday, October 22, 2007 10:21 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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?
10/22/2007 5:12 PM | georgev
Gravatar

# 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?
10/22/2007 5:14 PM | Mladen
Gravatar

# 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
10/22/2007 5:14 PM | georgev
Gravatar

# 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.
10/22/2007 5:15 PM | Mladen
Gravatar

# 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
10/22/2007 8:19 PM | ML
Gravatar

# 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 ....
10/22/2007 8:26 PM | Mladen
Gravatar

# 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
10/22/2007 10:44 PM | ML
Gravatar

# 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.
10/22/2007 10:48 PM | Mladen
Gravatar

# 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
10/22/2007 10:58 PM | ML
Gravatar

# 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 :)
10/22/2007 11:06 PM | Mladen
Gravatar

# 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.
10/23/2007 8:35 AM | Chris
Gravatar

# re: SQL Server: The one and only locale insensitive date format

good point chris.
10/23/2007 1:30 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET