However, in some cases the date must be in a DD/MM/YY format because many countries/regions 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.
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
Sets the first weekday to a number from 1 through 7. The U.S. English default is 7 (Sunday).
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 ServerTo 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',
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
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:
Article ID: 173907 - Last Review: Jun 22, 2014 - Revision: 1