INF : How to Set the Day/Month/Year Date Format in SQL Server

ID: Q173907

The information in this article applies to:

Microsoft SQL Server version 6.5

SUMMARY

By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed. This setting is fine for cases when an application that requires this functionality is deployed in a manner guaranteeing that dates are used and inserted in the same format across all platforms and locations where the application is used.

However, in some cases the date must be in a DD/MM/YY format because many countries use this format rather than the U.S. default of MM/DD/YY. This is especially an issue for international applications that are distributed all over the world.

MORE INFORMATION

If the date format is not taken into consideration by application developers, a rare situation may arise where an application is inserting dates into tables or using dates in WHERE clauses that are invalid. For example, a given date like 20/05/97 will only be processed if the date format is DD/MM/YY. However, a date like 12/05/97 will be processed with both the DD/MM/YY and MM/DD/YY formats, possibly resulting in the wrong date being used.

A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). Using the ISO format is more "international," and is independent of the default language. For more information, see the CONVERT function in the SQL Server Books Online.

Another solution is for the client application to check the date format being used on the SQL Server, to make sure that the dates passed while executing are in a valid format.

SQL Server provides the ability to set the date format and other language settings by adding another language. Just setting the regional setting in the Windows NT Control Panel to the local region's date format will not help in using dates in the DD/MM/YY format for SQL Server.

To use the DD/MM/YY format, use either of the following methods:

Use the SET Statement Per Connection

SET DATEFIRST <number>

Sets the first weekday to a number from 1 through 7. The U.S. English default is 7 (Sunday).

SET DATEFORMAT <format>

Sets the order of the date parts (month/day/year) for entering datetime or smalldatetime data. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.

This method allows you use a date format for dates sent to SQL Server of d/m/y, but it is connection dependent. If a new connection is made to SQL Server or if the server is stopped and restarted, the date format goes back to m/d/y.

Set the Language on the SQL Server

To set the language on the server you must add a language by using sp_addlanguage. The example below sets the language for British English and gives the dates in DD/MM/YY format. The example can also be applied to other countries, but you may need to modify the parameters for sp_addlanguage.


   exec sp_addlanguage 'British', 'English',
   'January,February,March,April,May,June,July,August,September,October,
   November,December',
      'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
   'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday',
   dmy,1
   sp_configure 'default language', 1
   reconfigure with override

To set the default language back to U.S. English after having installed another language, use the following SQL statements:


   sp_configure 'default language', 0
   reconfigure with override

To check what default language a server has installed, use the following SQL command:


   sp_configure 'default language'

If the resulting value is 0, the default language U.S. English. If the result is not 0, run the following SQL command to find the installed default language setting and date format used:


   select name ,alias, dateformat
   from syslanguages
      where langid =
      (select value from master..sysconfigures
         where comment = 'default language')

SQL Server also supports multiple languages, by setting the language in SQL Server Setup. This requires the use of localization files that are available for most languages. For more information, please read the following article in the Microsoft Knowledge Base:

Q169749 : INF: Installing Additional Languages on SQL Server

Additional query words: mdy datetime Aust Australia instlang.sql US

Keywords : kbenv SSrvGen
Version : WINNT:6.5
Platform : winnt
Issue type : kbhowto