Writing International Transact-SQL Statements

Databases and database applications are more portable from one language to another, or will support multiple languages, if these tips are followed:

• Replace all uses of the char, varchar, and text data types with nchar, nvarchar, and ntext. This eliminates any need to consider code page conversion issues.
• When performing month and day-of-week comparisons and operations, use the numeric dateparts rather than the name strings. Different language settings return different names for the months and week days. For example, DATENAME(MONTH,GETDATE()) returns May when the language is set to U.S. English, Mai when the language is set to German, and mai when the language is set to French. Instead, use a function such as DATEPART that uses the number of the month instead of the name. Use the datepart names when building result sets to be displayed to a user, because the date names are often more meaningful than a numeric representation; however, do not code any logic that depends on the displayed names being from a specific language.
• When specifying dates in comparisons or for input to INSERT or UPDATE statements, use constants that are interpreted the same for all language settings:
  • ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:
    { ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
    { d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
    { t 'hh:mm:ss'} such as: { t '10:02:20'}
  • Applications using other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the unseparated numeric strings for example: yyyymmdd as 19980924.
  • Applications using other APIs, or Transact-SQL scripts stored procedures, and triggers can also use the CONVERT statement with an explicit style parameter for all conversions between the date and smalldate data types and character string data types. For example, this statement is interpreted the same for all language or date format connection settings:

  SELECT *

  FROM Northwind.dbo.Orders

  WHERE OrderDate = CONVERT(DATETIME, '7/19/1996', 101)

  For more information, see CAST and CONVERT.