Converting Date and Time Data

The designers of a globalized application and its database must address how date and time information is standardized and converted. All date and time information must be converted to the same standard before it is written to the database to ensure that the information is interpreted and compared correctly in the future. The Formats component in NLS.dll (see About the Formats Component), which resides on the business-services tier, converts date and time information to and from the Eval database. If the locale and time zone information on the user's computer specifies a date format that does not match the one in effect on the SQL Server installation on which the database resides, the date is converted and the time data is changed to a 24-hour format and converted to Greenwich mean time (GMT).

To make an application portable from one language to another, or to make it able to support multiple languages, consider the following when writing queries that contain date and time data:

• Use the DATEPART function that uses the number of the month instead of the name string when performing month and day-of-week comparisons and operations.
• Use constants that are interpreted the same way for all languages when specifying dates for input to INSERT or UPDATE.
• Do not code logic that depends on displayed names being from a specific language.

Note  To see the date format and the name of the locale in effect on an installation of SQL Server, select the master database on the server and run the following query from the SQL Server Query Analyzer:

Select name, alias, dateformat from syslanguages where name = @@language