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

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')