05 January 2008 09:19 tonyrogerson

Date format inconsistency YYYY-MM-DD or YYYYMMDD? When 2007-04-10 is 4th October!

Most days I have battles with celko usually over use of IDENTITY, the latest and greatest his unacceptance that views are nothing more than inline expansion of the view metadata but more and more it's this particular formatting of dates, I've told him over and over again this problem exists yet in his reply I get called a Barbarian using nothing but local dialect or his latest rambling below...

"Well, as soon as the village of Cheapshire conquers the ISO Empire and
changes the Standards, I will do that.  And I will convert my liters
to firkins and pay my bills with shillings and old pence, too!  Long
live Queen Victoria!  LOL!"

Even though the date format to fix this problem is still ISO standard, I guess the guy has a level of ignorance and arrogance that won't allow him to accept he's wrong and thus modify his examples, after all he's been posting that format for a couple of decades now.

If I get to a point in my career where I stop listening will somebody please tell me as that is the point where you stop learning, your knowledge stops expanding and your experience becomes limited.

The Problem

When is 2007-04-10 the 4th October? When the login is using the language British!

Take a look at this inconsistency ->

set language British

select cast( '2007-04-10' as datetime )
-----------------------
2007-10-04 00:00:00.000

set language us_english

select cast( '2007-04-10' as datetime )
-----------------------
2007-04-10 00:00:00.000

go

The Fix

Fixing this problem is easy, you have two formats that are still ISO standard, those are YYYY-MM-DDTHH:MM:SS and YYYYMMDD.

set language British

select cast( '20070410' as datetime )
-----------------------
2007-04-10 00:00:00.000

set language us_english

select cast( '20070410' as datetime )
-----------------------
2007-04-10 00:00:00.000

go

Additional

Aparently according to celko I've to read the ISO standard because there is only provision for using dashes; anyway here it is for further reading and yes the standard makes provision for dashes (deemed for human readability) and without dashes (deemed I guess for machine readability):

http://www.iso.org/iso/support/faqs/faqs_widely_used_standards/widely_used_standards_other/date_and_time_format.htm#how-it-works

"Representations can be in one of two formats: a basic format that has a minimal number of characters and an extended format that adds characters to enhance human readability. For example, the third of January 2003 can be represented as either 20030103 or 2003-01-03."

Yes, I find it shocking too that somebody who literally rams the standards down our throats on the public forums should be so out of date on this issue.

It will be interesting to see if he actually changes his examples now (we live in hope....).

 

Filed under:

Comments

# re: Date format inconsistency YYYY-MM-DD or YYYYMMDD? When 2007-04-10 is 4th October!

06 January 2008 03:00 by vmvadivel

True Tony. I always tell my team that 'Always learn a subject as-if you don't know it only then you would learn the concepts better. If you go with a preloaded mindset of "I" know everything then there is a high possibility that you will end up learning nothing'

# re: Date format inconsistency YYYY-MM-DD or YYYYMMDD? When 2007-04-10 is 4th October!

07 January 2008 09:32 by AndyC London

Surely there is no such language as "British", the language should be simply "English" with the alternative being "American".

It's definately a bug, no one in the UK would ever want a format of YYYY-DD-MM, as that's reversed American format hence hence not part of our Imperial Standards Organisation formats.

On a more serious note, you need to be very careful with long formats for dates as we've been scuppered by France and Denmark with their use of a "." as a separator for dates and having different abreviations for months, e.g. 4 Okt 2007 in your ecample.

# re: Date format inconsistency YYYY-MM-DD or YYYYMMDD? When 2007-04-10 is 4th October!

10 January 2008 16:25 by Jamie Collins

Tony,

FWIW I believe that when Celko refers to 'ISO dates' he actually means ISO 9075 (i.e. the SQL standard -- specifically the 1992 version) rather than ISO 8601 (the date standard).

Jamie.

--

# re: Date format inconsistency YYYY-MM-DD or YYYYMMDD? When 2007-04-10 is 4th October!

10 January 2008 16:34 by tonyrogerson

I wish he'd say that - he keeps quoting ISO 8601 in his rants.

He's still not answered how he would advise a British client whether he'd tell them to use US English or if he'd do it properly - tell them to use YYYYMMDD and British.

He's still putting out the format that doesn't work here in the UK though - talk about unprofessional; knowing there is a problem yet not conceeding the point - buyer beware on his client services frankly.

# re: Date format inconsistency YYYY-MM-DD or YYYYMMDD? When 2007-04-10 is 4th October!

11 January 2008 08:28 by Jamie Collins

Oh I'm pretty sure he knows to tell them to use a safe format in practice. Remember, though, he has a public persona for use in the newsgroups which means he must be *seen* to be using Standard SQL where possible (Hugo's point in the ng about the Standard requiring the DATE keyword notwithstanding).

Jamie.

--

# Need help with a query | keyongtech

18 January 2009 17:37 by Need help with a query | keyongtech

Pingback from  Need help with a query | keyongtech