Re: mm/dd/yyyy format question


I thought about just letting this one die, but ...

1. Internal/external date/time representations:
----------------------------------------------------------------------
I was not talking about how .Net or SQL Server stores date/time values
internally. Only how those values are formatted in the twilight zone
between applications so they could be interpreted unambiguously.

2. ISO 8601 usage:
----------------------------------------------------------------------
I get the impression that you are talking about ISO 8601 in terms
of what areas of the world uses the format for human-readable
date/time information. Again, I am only concerned about where
applications use the format for unambiguously representing date/time
values across interfaces.

Main entry: http://en.wikipedia.org/wiki/ISO_8601
Some adopters of the format: http://www.qsl.net/g1smd/isoimp.htm
Campaign: http://www.saqqara.demon.co.uk/datefmt.htm
....and all the other links from the Wikipedia article.

In .Net, the ISO 8601 format is used when serializing date/time values
to XML. No specific settings. This is the default format.

Going outside Microsoft:

MySQL: http://www.php-faq.de/q/q-datum-mysql.html
"MySQL verarbeitet Datumsangaben im ISO-8601-Format (siehe die
Abhandlung von Markus Kuhn zu diesem Thema). Dies ist das offizielle
deutsche Datumsformat, eine Umwandlung ist nicht notwendig, weil nicht
normgerecht."
Support was broken for a short while, but has been fixed now:
http://bugs.mysql.com/bug.php?id=7308

XML Schema: http://www.w3.org/TR/xmlschema-2/#isoformats

Oracle seems to happy to work with the ISO 8601 format:
http://www.oracle.com/technology/products/jdev/htdocs/partners/addins/exchange/jsf/doc/tagdoc/core/validateDateTimeRange.html

IBM:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0510roy/

Atom specification:
http://www.atomenabled.org/developers/syndication/atom-format-spec.php

....and I could Google all day long.

So my question to you (Cor) is: If you send a query to SQL Server (or
another database), how do you format your date/time values, e.g. if
the query goes something like
"Select <fields> From Orders Where OrderDate >= '<somedate>'",
how would you write the '<somedate>' portion to ensure that it
succeeds, regardless of client/server configuration?

*That* is what I have been talking about all along.

If you were talking about something else, ok, we have two
different discussions. If the same, let's just agree to disagree.

Merry Christmas 'n'all!

Joergen Bech





On Wed, 21 Dec 2005 18:54:33 +0100, "Cor Ligthert [MVP]"
<notmyfirstname@xxxxxxxxx> wrote:

>Joergen,
>>
>> The original question stated
>> "what is the logical way to configure server, sql server or program so
>> it always deals with date as mm/dd/yyyy format".
>>
>The SQL server nor a Net program do deal with a string format. Thet deal
>with different ticks.
>SQL server has ticks from 1000/3 millisecond starting at 1-1-1753 (for the
>DateTime)
>Net deals with ticks from a unit of hundred nanosecond starting at 1-1-1
>
>> And I also said that I recommended the ISO approach when
>> "passing date information as a string" (I use it myself and have
>> never looked back).
>>
>
>I did as well however Net has better methods. By using direct the internal
>DateTime and only use the string when it is needed to show it on screen or
>get it from the sceen.
>
>> I know that .Net takes care of all this provided that one goes
>> through the proper objects.
>>
>> ISO 8601 specific to Asian countries? This is the first time I
>> have heard that. Can you provide me with some links to some
>> more information?
>
>That is not so strange as you think that they don't use European languages.
>The former communist countries have used it a while, however that is as well
>gone again. I think that the Britans will drive earlier on the right lane
>than ISO8601 is used in Europe. China started the Georgian calendar late in
>1949 and Turkey in 1928. Most Islamic countries (not all see Turkey) have of
>course there own calendar (Isreal as well an hebrewic). Therefore the main
>ISO8601 countries are India and China. From other Asian countries I am not
>sure what they use.
>
>Searching for Calendar and ISO8601 in Google gives you a bunch of
>information. So wants the state of Nevada only use ISO8601 in official
>websites.
>
> >As the article in the Microsoft link stated,
>> the ISO format is unambiguous to SQL Server and is recommended
>> for that reason, regardless of the locale settings or country.
>
>The article is clear for me from a probably SQL part of Microsoft, not from
>the Net or the OS part. How would you look to it if it was writen by
>Navision/Microsoft.
>
>> I only mentioned the ISO format because other posters started
>> talking about mm-dd-yyyy and dd-mm-yyyy.
>
>That I understood, however those dates have given often misunderstandings,
>while it is so fine done in Net.
>
>> If the original question had been about what .Net classes/objects to
>> use in order to pass date information correctly to and from SQL
>> Server, I suppose we would have had an entirely different
>> discussion.
>>
>The way as Armin wrote it was in my opinon clear for the question. That was
>why I did not add anything beside the warning to it, while I mostly do that
>in DateTime questions.
>
>(In my opinion is it a pity that the VBNet IDE shows all dateTimes in USA
>format, whatever it internal is. C# shows it in the culture format from the
>system, my favorite would be as it was showed in ISO8601 in all IDE's,
>however that is noted as point for the future)
>
>I hope that you now understand why I gave a reply on your message.
>
>Cor
>

.

Relevant Pages

  • Re: mm/dd/yyyy format question
    ... "what is the logical way to configure server, sql server or program so ... it always deals with date as mm/dd/yyyy format". ... ISO 8601 specific to Asian countries? ... If the original question had been about what .Net classes/objects to ...
    (microsoft.public.dotnet.languages.vb)
  • Re: can anyone tell me how to use DTS to convert a ISO date time to JAVA long type?
    ... > There is a column which type is ISO DATE type in my source DB, ... "format", but take the input and store it in an internal format, such as ... If you are working with dates as strings, or date to string then you can ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: Problem with querying on a date filed
    ... You need to delimit the date - ... SQL Server like this ... I didn't show the rest of the code as I'm pretty certain the problem is in my format of the date field. ... G_Date is formatted as date/time. ...
    (microsoft.public.frontpage.client)
  • Re: mm/dd/yyyy format question
    ... The back end database is a SQL Server and I like ... >it always deals with date as mm/dd/yyyy format. ... Use the ISO 8601 format when passing date information as a string to ...
    (microsoft.public.dotnet.languages.vb)
  • Re: VB Date formating Update problem
    ... Use the Format() function in VB and produce a string which is either unseparated or the ISO 8601 ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.odbc)