Change Default Language/ Date Format in SQL

By | July 27, 2009

Usually in SQL, date gets saved as ‘2009-07-27 00:00:00.000’ but when we run the query or call date from any application the SQL gives us the date in desired format like ’07/27/2009’. How does it work?

SQL has set of formats according to the different languages for example ‘British’, ‘Dutch’, ‘German’, ‘English’ etc.

By Default SQL server set to ‘English’ language so its date format is ‘mdy’, so by default when you execute any query with this date format it reads the date accordingly.

But now if you want to change the format of reading the date itself then we have to change the default language for SQL.

Here are the commands

sp_helplanguage This will give you the list of diferent languages exist in SQL.

To change the default Language see the below mentioned command
sp_configure ‘default language’,

here we can change the default lanuage to any language of our choice for ecample to change it to British we will write it as
sp_configure ‘default language’,23
23 is number of British language record

Now we have to change the same for users login
sp_defaultlanguage @loginame = ‘login’ ,@language = ‘language’

to change the langueg for user ‘Sa’ to british we will write it as
sp_defaultlanguage @loginame = ‘sa’ ,@language = ‘British’

If you find this content useful, Please drop us an email at [email protected].