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

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 6, 2006. If user B has a default language of ‘British’, then the date will be interpreted as June 4, 2006.

So far, I’m just presenting some background information and haven’t really solved the problem.

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:

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

I hope you find this useful.