SET DATEFIRST and SET LANGUAGE

This is a tricky part. SQL Server have language settings at the following levels;

1.       Server

2.       User Login

3.       Session

You need to be very clear for the language settings of the user / login as it may impact the date functions;

DECLARE @Today DATETIME; 

SET @Today = getdate(); 

 

SET LANGUAGE Italian; 

SELECT DATENAME(month, @Today) AS 'Month Name'; 

Select @@DATEFIRST

 

SET LANGUAGE us_English;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE British;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE us_English;

GO 

As you can see from the above code, I have set the language back to US English (English in SQL Server language drop downs) as I like to have it that way. You also see that @@DATEFIRST function returns different value for different languages. This value is set on the basis of;

1.       Language selected

2.       SET DATEFIRST option

You can change the language and still keep the First date of week same as in US English;

SET LANGUAGE us_English;

Select @@DATEFIRST

SET LANGUAGE British;

Select @@DATEFIRST

SET DATEFIRST 7

Select @@DATEFIRST

As you can see, US_English sets the @@DateFirst to 7 and British sets it to 1. But we have used SET DATEFIRST to change the language to British but kept @@DATEFIRST  to 7 using SET DATEFIRST option.

This is very important when you are working multilingual databases. Where ever you are using day of week values (in DatePart, etc), you should keep this change in mind as well. Either use one language throughout your database for Server, users and session. But if there might be any change to get the benefit of SQL Server multilingual support, you should use SET DATEFIRST into account when programming to keep all the calculations and checks aligned.