How to Set DateFirst SQL Function Return for a SQL Server Database Scope Permanent
Just like @@DATEFIRST session variable, DATEFIRST sql function is also frequently used by t-sql developers or database administrators in SQL Server.
I code using @@DATEFIRST and DATEFIRST especially identifying the non-work days like weekends in a week.
I have several user defined functions using this t-sql DateFirst commands. You can refer to How to Get Week Day Name using T-SQL in MS SQL Server and How to Calculate the Count of Working Days Between Two Dates using T-SQL User Defined Function for samples on tsql @@DateFirst coding.
Using SET DATEFIRST sql statement is important in such conditions since SQL Server DATEFIRST property changes per session connection and each connection can be defined with different languages causing the @@DateFirst variable to return different values.
Instead of working with sessions everytime, developers or sql database administrators might want to define globally for a MS SQL Server database DateFirst or set DateFirst sql default value permanently.
In fact the default sql DateFirst return value depends on an important parameter which is the default language of the current connection. The default language of a sql connection is defined by the sql user language properties. These languages are listed in the sys.syslanguages.
We will talk about the sys.syslanguages in the following sections.
SQL SET Command
The SET transact-sql command enables t-sql developers and SQL Server database administrators (DBAs) set and change the session specific parameters that affects the handling of specific information.
Sql SET DATEFIRST statement is one of the session configuration t-sql commands for sql developers and sql administrators.
SET DateFirst SQL Server T-SQL Command
SET DateFirst SQL Server t-sql statement sets the first day of a week for the current session.
The first day parameter is set by SET DateFirst using one of the numbers from 1 to 7 each is mapping to a day of the week.
The default value for a US English SQL Server login user the DateFirst parameter equals to 7 (Sunday) by default.
The day number that will be used in SET DateFirst is as follows :
| DateFirst n | Name of the First Day of Week |
| DateFirst 1 | Monday |
| DateFirst 2 | Tuesday |
| DateFirst 3 | Wednesday |
| DateFirst 4 | Thursday |
| DateFirst 5 | Friday |
| DateFirst 6 | Saturday |
| DateFirst 7 | Sunday |
The SET DateFirst t-sql syntax is shown below where number if from 1 to 7 and @number_var is an integer variable.
SET DATEFIRST { number | @number_var }
SET DATEFIRST 1 -- For Turkish (T�rk�e)
SET DATEFIRST 1 -- For Italian (Italiano)
SET DATEFIRST 3 -- For German (Deutsch)
SET DATEFIRST 7 -- For US English (us_english)
SET DateFirst Permanently or SET DateFirst as SQL database property globally
In fact, as mentioned above before the DATEFIRST property is specific to each SQL connection.
So using the SET DATEFIRST command just as other SET commands, @@DATEFIRST property can be set for a sql login or sql user which will effect the all connections of the related user.
The DATEFIRST property or @@DateFirst variable value is configured by the default language of the connected user.
To see valid languages for sql connections you can browse the sys.syslanguages system table.
SELECT * FROM sys.syslanguages
Here in the above table I believe you have realized the datefirst column in the sys.syslanguages table.
You can alter the default language of a sql login or windows user authenticated on SQL Server using the Microsoft SQL Server Management Studio or executing the below sample t-sql script :
USE [master]
GO
ALTER LOGIN [DE\kodyaz] WITH DEFAULT_LANGUAGE = [Deutsch]
GO
By executing the above t-sql ALTER LOGIN statement on the master database you will change permanently the DateFirst sql parameter value for the related user globally.