You are currently offline, waiting for your internet to reconnect

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

Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
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 settingis fine for cases when an application that requires this functionality isdeployed in a manner guaranteeing that dates are used and inserted in thesame format across all platforms and locations where the application isused.

However, in some cases the date must be in a DD/MM/YY format because manycountries/regions use this format rather than the U.S. default of MM/DD/YY. This isespecially an issue for international applications that are distributed allover the world.
More information
If the date format is not taken into consideration by applicationdevelopers, a rare situation may arise where an application is insertingdates into tables or using dates in WHERE clauses that are invalid. Forexample, a given date like 20/05/97 will only be processed if the dateformat is DD/MM/YY. However, a date like 12/05/97 will be processed withboth the DD/MM/YY and MM/DD/YY formats, possibly resulting in the wrongdate being used.

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

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

SQL Server provides the ability to set the date format and other languagesettings by adding another language. Just setting the regional setting inthe Windows NT Control Panel to the local region's date format will nothelp 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. Englishdefault is 7 (Sunday).
SET DATEFORMAT <format>

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

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

Set the Language on the SQL Server

To set the language on the server you must add a language by usingsp_addlanguage. The example below sets the language for British English andgives the dates in DD/MM/YY format. The example can also be applied toother countries, but you may need to modify the parameters forsp_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 installedanother 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 followingSQL command:
   sp_configure 'default language'				

If the resulting value is 0, the default language U.S. English. If theresult is not 0, run the following SQL command to find the installeddefault 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 SQLServer Setup. This requires the use of localization files that areavailable for most languages. For more information, please read thefollowing article in the Microsoft Knowledge Base:
169749 : INF: Installing Additional Languages on SQL Server
mdy datetime Aust Australia instlang.sql US
Properties

Article ID: 173907 - Last Review: 06/22/2014 18:30:00 - Revision: 5.0

Microsoft SQL Server 6.5 Standard Edition

  • kbsqlsetup kbenv kbhowto KB173907
Feedback
/html>html>="display:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">