Re: Date format for SQL Select Statements and Internationalization

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 22:13:35 +0200

Regional setting has nothing to do with how SQL Server interprets datetime strings. The language for
a login does, and also, SET DATEFORMAT and SET LANGUAGE.

Two setting independent formats in SQL Server are unseparated 'YYYYMMDD hh:mm:ss' and ISO 8601
format 'YYYY-MM-DDThh:mm:ss'. What you refer to as the ISO format is *not* setting independent!

The regional setting of the client can of course affect all this if you just pass a date variable
straight to SQL Server without passing it through a proper format function.

http://www.karaszi.com/SQLServer/info_datetime.asp 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hubbard" <Mike Hubbard@discussions.microsoft.com> wrote in message 
news:C7E9C538-6FCF-402C-A0B5-E35B377F4A0E@microsoft.com...
> Is there a proper (read standard) format that we should be passing date/times
> to SQL Server queries so that regardless of a user's Regional Settings or the
> version of SQL Server (i.e. domestic MM/DD/YYYY HH:MM:SS or Int'l DD/MM/YYYY
> HH:MM:SS) the query will be properly executed?  Currently, regardless of the
> regional settings of the user's PC or the language setting of SQL Server, the
> only format that works between the two is MM/DD/YYYY HH:MM:SS.
>
> What if we send all dates and times in ISO format YYYY-MM-DD HH:MM:SS?  Will
> this format allow us to use this standard format irrespective of a user's
> regional settings or the version of SQL Server, and have it properly executed
> by SQL Server?

Relevant Pages

  • Re: DateTime Format in Localized version of MSDE
    ... How do we determine the date time format in a SQL Server instance. ... Specifically I would like to know, if the date time data type in SQL Server ... is Language Specific or Language Neutral. ... However under MSDE 2000 / SQL Server 2000 the collation order ...
    (microsoft.public.sqlserver.msde)
  • Re: ISO SQL Date Format / ISO Date Formats - question to --CELKO-- on your conti
    ... DD-MM-YYYY, yes, but I've never seen YYYY-DD-MM ... But first, lets just agree on that how datetime literals like these 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, ... the problem is that SQL Server tries to be "smart" and extract the year part even if in the wrong place. ...
    (microsoft.public.sqlserver.programming)
  • Re: ISO SQL Date Format / ISO Date Formats - question to --CELKO-- on your conti
    ... 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. ... It would be utterly perverse to parse dates this way, since it directly subverts the purpose of ISO dates. ... No language installed with SQL Server has this format. ...
    (microsoft.public.sqlserver.programming)
  • Re: DateTime Format in Localized version of MSDE
    ... You are right that datetime and smalldatetime in SQL Server are stored in ... a binary, language-neutral format. ... deleting depends on the language setting for the connection, ... collation is irrelevant for datetime. ...
    (microsoft.public.sqlserver.msde)
  • Re: DateTime Automatic Formatting
    ... datetime to a string in a format suitable for the Language of the ... but it's simple and all the "report" ... the format of the report would have to be seperate from the sql to ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)