ISO SQL Date Format / ISO Date Formats - question to --CELKO-- on your continued misuse of the inconsistent date formats in your advice
Tony Rogerson
YYYY-MM-DD and YYYYMMDD.
The ISO SQL Standard date format is YYYY-MM-DD only which is termed a "date
literal".
According to the standard (see here:
http://savage.net.au/SQL/sql-92.bnf.html#date literal) the "date literal" in
order for it to be a "date literal" MUST be prefixed by the keyword DATE, if
not then the text is simply nothing more than a "string literal" which has
different meaning and subject to regional settings if used on data type
conversions ie. string literal to date literal.
So, in order for us to have a defined ISO SQL Standard date in our queries
we must use the syntax DATE 'YYYY-MM-DD'.
String literals on the other hand are subject to regional settings and
because its a string literal can accept any ISO standard date format so
YYYYMMDD is indeed legal.
YYYYMMDD has no grounds for confusion and the simple conversion from a
string literal to a date literal happens consistently.
Now - CELKO, given the above why the hell do you keep pushing YYYY-MM-DD as
a date format on the SQL Server forum when you know only to well that it is
not consistent through regional settings - in the US YYYY-MM-DD works fine
but in a lot of European countries it doesn't and is actually read as
YYYY-DD-MM.
You have no excuse at all - your statement that YYYY-MM-DD is the only
standard SQL date format whilst true is of little use because SQL Server has
no DATE '{date literal}' syntax and I'm not aware that all the major
databases have it either so although true - you cannot use it; also - your
posts neglect to show the prefix of DATE.
Being somebody who hammers the standard into people - how can you yourself
not accept what it states in black and white in the Standards?
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
Electro Lytes
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:31A88183-0085-4BD6...@microsoft.com...
Because in his warped mind, the world revolves around CELKO.
mouser
On 18-Nov-2009, "Tony Rogerson" <tonyro...@torver.net> wrote:
> Being somebody who hammers the standard into people - how can you yourself
>
> not accept what it states in black and white in the Standards?
CELKO is a troll, that's why.
--CELKO--
Correct. But <date literal> and <date value> are not quite the same:
<date literal > ::= DATE <date string>
<date string> ::= <quote> <date value> <quote>
<date value> ::= <years value> <minus sign> <months value> <minus
sign> <days value>
This is why you can pass pass date parameters as strings to procedures
without the keyword DATE. While casting will take care of this in
most cases, I agree that it is better practice to use the keyword for
documentation and it is one of the changes I want to make in the
fourth edition of SQL FOR SMARTIES (ISO data element names is the most
painful one).
>> String literals on the other hand are subject to regional settings .. <<
The regional settings are a vendor concept; the standard has COLLATION
for language related stuff and that is it.
>> .. and because its a string literal can accept any ISO standard date format so YYYYMMDD is indeed legal. <<
Again, you missed the point (I think on purpose); Standard SQL has
one and only one allowed <date value> format. And Microsoft is moving
away from the old Sybase code museum toward it, as shown by the DATE
data type defaults . This will make them compatible with DB2, Oracle,
all the open source database I know and most of the world's other
software and data.
Would I like to have all the SQLs also accept the 'YYYYMMDD' solid
string format (with T and Z for time and zone stuff)? Yes, and I plan
to push it in 2010.
>> .. has no grounds for confusion and the simple conversion from a string literal to a date literal happens consistently. <<
So does the SQL Standard format. And string-to-date conversion is not
that simple (date ranges within month, month sizes within year, etc.)
compared to string-to-integer (classic recursive ten lines of assembly
code).
A better reason is that the solid string has no white spaces in it.
This is important when you look at other ISO Standards and commercial
products that have a line wrap display and/or consider all white
spaces the same. Pressure from the EU will push this.
I have a feeling that after the "new" temporal data types have been in
place in SQL Server so that people are weaned from the "Sybase Museum"
in a few releases you will see ISO Standard dates included with all
regional settings. This is an impression I got at PASS an SQL
Connections and is not a violation of an NDA, or a DNQ for the
record.
Tony Rogerson
moment to fully answer your post because I'm at sqlbits.
But - have you actually read books online and the definition of DATE?
It does return YYYY-MM-DD as a "display format"; so - enter in here your
continued rant about display being done in the front end.
I'm not interested in the output string so long as its standard and
consistent.
I am interested in the input string, YYYY-MM-DD has inconsistency problems
(as you well know and ignore because of your overwhelming arrogance) across
the product versions and will do across all code written to date - a lot of
that code will remain untouched as is going through the versions because MS
will never retire that inconsistency - believe me - we've tried and they
have to keep it there for compatibility reasons.
Now, go and look at books online - DATE takes any number of input formats,
the two I'm interested in are below....
ISO 8601 Descripton
YYYY-MM-DD
YYYYMMDD
Same as the SQL standard. This is the only format that is defined as an
international standard.
I'm absolutely positive that people in the industry accept that because of
legacy inconsistency YYYYMMDD is the correct input format to use on systems
because it allows a move towards the DATE type but also keeps consistency
with legacy systems and doesn't cause $100;sK in resource for unnecessary
development and testing.
You on the other hand are so far up your own bottom to see and accept that!
--ROGGIE--
"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:31A88183-0085-4BD6...@microsoft.com...
Aaron Bertrand
> without the keyword DATE.
But that doesn't explain why you continue telling users to pass those
strings using the format 'YYYY-MM-DD' when you've been told many times that
this format is not guaranteed to work consistently across different
installations of SQL Server.
> The regional settings are a vendor concept; the standard has COLLATION
> for language related stuff and that is it.
The users you are giving "answers" to are using a vendor-provided product.
Whether or not their implementation is derived directly from the standard is
irrelevant; you are giving them bad code that will fail on their systems.
If they were just taking a theory class, and you were describing to them,
"here is what you would pass to the database if it adhered to the standards,
only to the standards, and this regional settings nonsense was a figment of
your imagination," then maybe it would be okay. But obviously that is not
the case.
> Would I like to have all the SQLs also accept the 'YYYYMMDD' solid
> string format (with T and Z for time and zone stuff)? Yes,
I don't believe you. I've been arguing with you for years that when you
push YYYY-MM-DD on users (this is actually the first time I recall seeing
you even *acknowledge* that there is a YYYYMMDD format), you are potentially
giving them very bad advice. Imagine one of these posters working at a
hospital in France, and you insist that they use YYYY-MM-DD. They enter a
kidney transplant date of 2010-01-10, but it actually gets stored as October
1st because of their regional settings. Something tells me that transplant
isn't going to go very well. For shame.
A
mouser
On 19-Nov-2009, "Tony Rogerson" <tonyro...@torver.net> wrote:
> You on the other hand are so far up your own bottom to see and accept
> that!
Gazill Yanaire
"Aaron Bertrand" <moc....@dnartreb.noraa> wrote in message
news:C72C7EB1.27792%moc....@dnartreb.noraa...
CELKO is a self absorbed idiot. Ignore his rants.
Jeroen Mostert
> Now - CELKO, given the above why the hell do you keep pushing YYYY-MM-DD
> as a date format on the SQL Server forum when you know only to well that
> it is not consistent through regional settings - in the US YYYY-MM-DD
> works fine but in a lot of European countries it doesn't and is actually
> read as YYYY-DD-MM.
>
DD-MM-YYYY, yes, but I've never seen YYYY-DD-MM. It would be utterly
perverse to parse dates this way, since it directly subverts the purpose of
ISO dates (anything with the year first ought to be parsed as ISO). SET
DATEFORMAT YDM will allow you to enter the mouth of madness, but anyone who
uses this arguably deserves what's coming to them. No language installed
with SQL Server has this format.
The perversity of this was fortunately realized by the SQL Server team, as
they made this particular format impossible for the new date/time types.
Also, using the full ISO 8601 notation will yield the correct date (or
rather datetime) regardless of any locale settings: '2009-01-09T00:00:00'
always means "midnight, January 9th, 2009". Thank heavens for small favors.
--
J.
Tony Rogerson
that's the point - the date IS passed as YYYY-MM-DD but because of langauge
settings it gets read as YYYY-DD-MM in regions where the default install is
British English for example.
Using YYYYMMDD is consistent on regional settings which is what we keep
trying to hammer into celko without success.
Tony
"Jeroen Mostert" <jmos...@xs4all.nl> wrote in message
news:4b073068$0$22938$e4fe...@news.xs4all.nl...
Tibor Karaszi
>
> DD-MM-YYYY, yes, but I've never seen YYYY-DD-MM
The short answer is that it doesn't matter. The long answer takes a bit
elaboration. But first, lets just agree on that how datetime literals like
these (separated by separator) are determined by settings for logins, SET
LANGUAGE and SET DATEFORMAT. And if a client passes the date in a format
which doesn't match (client app possibly affected by regional settings),
then we have a problem. Probably obvious, but just to be clear and also for
benefit to other readers.
Having said above, I do agree that there is not ydm format. I.e., below
query returns zero rows:
SELECT *
FROM sys.syslanguages
WHERE dateformat = 'ydm'
However, the problem is that SQL Server tries to be "smart" and extract the
year part even if in the wrong place. So, the question was: Can YYYY-MM-MM
be interpreted as YYYY-DD-MM?
And the answer is yes, if there is any language which has d before m. Lets
check:
SELECT *
FROM sys.syslanguages
WHERE dateformat LIKE '%dm%'
23 languages! Lets try one of them:
SET LANGUAGE 'Deutsch'
GO
SELECT CAST('2008-05-23' AS datetime)
Error message.
> SET DATEFORMAT YDM will allow you to enter the mouth of madness, but
> anyone who uses this arguably deserves what's coming to them. No language
> installed with SQL Server has this format.
But as we've seen other formats (dmy) share the very same issue as the ydm
format
> The perversity of this was fortunately realized by the SQL Server team, as
> they made this particular format impossible for the new date/time types.
> Also, using the full ISO 8601 notation will yield the correct date (or
> rather datetime) regardless of any locale settings: '2009-01-09T00:00:00'
> always means "midnight, January 9th, 2009". Thank heavens for small
> favors.
Agreed and agreed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeroen Mostert" <jmos...@xs4all.nl> wrote in message
news:4b073068$0$22938$e4fe...@news.xs4all.nl...
Aaron Bertrand
I try to. But a lot of newcomers take his word for gospel. He must know
what he's talking about, because he's written books.
Aaron Bertrand
>> Now - CELKO, given the above why the hell do you keep pushing YYYY-MM-DD
>> as a date format on the SQL Server forum when you know only to well that
>> it is not consistent through regional settings - in the US YYYY-MM-DD
>> works fine but in a lot of European countries it doesn't and is actually
>> read as YYYY-DD-MM.
>>
> Out of curiosity, which ones?
Try
SET LANGUAGE FRENCH;
SELECT DATEPART(DAY, '2009-01-02');
Aaron Bertrand
but YYYY-MM-DD is still transposed to YYYY-DD-MM:
SET LANGUAGE FRENCH;
SELECT DATEPART(DAY, '2009-01-02');
SELECT DATEPART(DAY, '20090102');
SET LANGUAGE ENGLISH;
Note that this has been partially fixed in 2008. But is it completely
solved? No. If you convert to datetime first, it still "fails":
SET LANGUAGE FRENCH;
SELECT DATEPART(DAY, '2009-01-02');
SELECT DATEPART(DAY, CONVERT(DATETIME, '2009-01-02'));
SELECT DATEPART(DAY, '20090102');
SET LANGUAGE ENGLISH;
On 11/21/09 10:06 AM, in article C72D6C43.27828%moc....@dnartreb.noraa,
Jeroen Mostert
> As an addendum, try this on 2000 or 2005. Note that it gets YYYYMMDD right
> but YYYY-MM-DD is still transposed to YYYY-DD-MM:
>
> SET LANGUAGE FRENCH;
> SELECT DATEPART(DAY, '2009-01-02');
> SELECT DATEPART(DAY, '20090102');
> SET LANGUAGE ENGLISH;
>
I'm not going to switch to using YYYYMMDD in my daily work, because we're
almost done migrating everything to SQL Server 2008 *and* we never change
the language setting *and* separated dates are easier to read, but this is
one of those gotchas you absolutely have to know about.
--
J.
--CELKO--
Yes, it is awful. I have had data transfers screwed up over the years
because of Sybase/SQL Server or Oracle or other data sources that
wrote dates in dialect and not in ISO-8601. I also did Y2K consulting
in the US and Turkey in the old days.
Yes, the separated format is much easier to read. And it is clearly
coming to SQL Server 20xx. EU regulations, ANSI/ISO pressure so that
data can be exchanged, etc.
I would seriously consider getting things right now and not have to do
it in a few years with more data. Let the application programmers
worry about changing all the month names into Bulgarian for the
displays :)
Erland Sommarskog
> I would seriously consider getting things right now and not have to do
> it in a few years with more data. Let the application programmers
> worry about changing all the month names into Bulgarian for the
> displays :)
Sorry, Celko, but there is on excuse for not using a safe date format.
Maybe in SQL 2030 when datetime has been deprecated and removed from the
product, YYYY-MM-DD will be safe. But note that datetime is not deprecated
now, so it may not ever happen.
Observe also, that the cases were you actually have to play with date
formats are not very common, ir you program properly. And in the situations
where it is, YYYYMMDD is the most convenient. (Because those are when you
need to find the first or last of the months, which is simple to achieve
with convert with style 112 and some substrings. User input should come
with parameters with binary format, where the text interpretation was
done on the client.
But the few times you do string literals in SQL Server, you should use
YYYYMMDD or YYYY-MM-DDThh:mm (or YYYY-MM-DDZ), but never YYYY-MM-DD alone.
It's an accident waiting to happen.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Aaron Bertrand
> it in a few years with more data.
But as Erland says, today you hammer into people that they should use
YYYY-MM-DD. When you've been told countless times that this format is NOT
safe in SQL Server. When you're responding to questions in a newsgroup
devoted to a particular vendor implementation, you need to make sure your
answers are appropriate for that vendor implementation, not some futuristic,
fantasy RDBMS you are dreaming about in la-la land.
Tony Rogerson
YYYYMMDD is ISO-8601
I fully understand that it pains you that for the past 20 years you have
been telling folk incorrect information.
I'm back from SQLBits tonight so will finally be able to research your
answer which I know is incorrect.
> Yes, the separated format is much easier to read. And it is clearly
> coming to SQL Server 20xx. EU regulations, ANSI/ISO pressure so that
> data can be exchanged, etc.
Correct ISO-8601 has ALREADY been adopted in the product.
YYYYMMDD is ISO-8601.
> I would seriously consider getting things right now and not have to do
> it in a few years with more data. Let the application programmers
> worry about changing all the month names into Bulgarian for the
> displays :)
Even though you know that YYYY-MM-DD isn't consistent and you've been shown
the proof by product experts countless times you STILL give this bad advice.
Talk about unprofessional - your arrogance amazes me.
But its more than that; you talk about us living in our gheoto yet you can't
see past the theory and think Austin Texas is the centre of the universe!
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:0f000325-cbbe-4163...@m26g2000yqb.googlegroups.com...
Cleo Baines Hafsteder
"Aaron Bertrand" <moc....@dnartreb.noraa> wrote in message
news:C72D6BF9.27826%moc....@dnartreb.noraa...
>> CELKO is a self absorbed idiot. Ignore his rants.
>
> I try to. But a lot of newcomers take his word for gospel. He must know
> what he's talking about, because he's written books.
>
After reading about CELKO, I would purposely avoid his books.