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: SQL Server