Date format for SQL Select Statements and Internationalization

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mike Hubbard (Hubbard_at_discussions.microsoft.com)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 13:03:07 -0700

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: 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: Date format for SQL Select Statements and Internationalization
    ... Regional setting has nothing to do with how SQL Server interprets datetime strings. ... a login does, and also, SET DATEFORMAT and SET LANGUAGE. ... straight to SQL Server without passing it through a proper format function. ... > to SQL Server queries so that regardless of a user's Regional Settings or the ...
    (microsoft.public.sqlserver.programming)
  • Re: Date Format
    ... yyyy-mm-dd by itself is not a safe format in SQL Server; ... interpretation, depending on the regional settings of the machine, as well ... The conversion of a char data type to a datetime data type resulted in an ...
    (microsoft.public.sqlserver.programming)
  • Re: mm/dd/yyyy format question
    ... I'd also suggest using the regional settings. ... > If the user enters dates, and if you want to force this input format, you ... > Concerning sql server: If you use parameters with SqlCommands, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Sorting dates
    ... The key word here is text. ... I have set up the short date in regional settings to this format. ... Dates are very important to properly place a person in the proper century. ...
    (microsoft.public.excel)