SQL Server Default Languages and Misinterpretation

Problem: It seems that the local settings have an effect on how SQL Server interprets incoming dates.  For e.g. Country A’s server may see dates in the format MM/DD/YYYY while country B may see it as DD/MM/YYYY by default.  This causes problems as a date and month with values lower than 12 most often results in mixed up dates.

Details

Each SQL Server has a default language.  You can see what the default language is by executing these commands (in Query Analyzer).

sp_configure ‘default language’

This will tell you what the default language is (sort of).  It actually returns a config_value with an integer that represents the language id. You can then run…

sp_helplanguage

You will see a list of languages that SQL Server supports. 

The odd thing here is that the server’s language setting will not solve your problem.  This setting configures the default language for NEW users.  By changing this setting, existing users will continue to have their original language.  This is where it gets interesting because it’s the login’s language setting that determines SQL Server’s date format.

For example, if user A has a default language of us_english, then a date of 4/6/2006 will be interpreted as April 4, 2006.  If user B has a default language of ‘British’, then the date will be interpreted as June 6, 2006.

The good news is that you can change the default language for a user so that subsequent logins will exhibit the correct interpretation of dates.  Here’s how:
You can set the default language for a user by issueing the following command.

sp_defaultlanguage @loginame = ‘LoginName’, @language = ‘Language’

After running this command, you will need to logout and back in to the database in order for the change to take affect.  The good news is that the language setting only needs to be done once (for each user in the database).

There is an alternative method, but it only works for the current session.  You can set the language in your query (much the same way the Set DateFormat works).  When you disconnect from the database, the language setting is NOT saved.  Set Language differs from Set DateFormat regarding weekday names and month names, for example:

CODE

set language ‘us_english’
Select Convert(DateTime, ‘4/6/2006’), 
       DateName(weekday, ‘4/6/2006’),
       DateName(Month, ‘4/6/2006’)

set language ‘Italian’
Select Convert(DateTime, ‘4/6/2006’), 
       DateName(Weekday, ‘4/6/2006’),
       DateName(Month, ‘4/6/2006’)

Summary

You can set the default language for new logins by configuring the server’s default language by using sp_configure.
You can change a user’s default language by using sp_defaultlanguage.
You can temporarily change the language for a query by using Set Language

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: