SQL - @@DATEFIRST Function

---

The SQL @@DATEFIRST function is used to retrieve the first day of the week which is set by SET DATEFIRST. The SET DATEFIRST is used to set the first day of a week. The first-day parameter (n) is set using one of the numbers from 1 to 7, where each number is mapped to a day of the week.

The day numbers will be as follows −

• Monday − 1
• Tuesday − 2
• Wednesday − 3
• Thursday − 4
• Friday − 5
• Saturday − 6
• Sunday − 7

Syntax

Following is the syntax of the SQL @@DATEFIRST function −

@@DATEFIRST

Parameters

This function does not accept any parameters.

Example

In the following example, we are trying to set the first day of the week value to 5 i.e. Friday −

Note − The SET DATAFIRST n specifies the first day (Monday, Tuesday, Wednesday, etc.) of the week. The value of n can be from 1 to 7.

SQL> SET DATEFIRST 5; -- (Friday)

SELECT @@DATEFIRST AS FIRST_DAY;

Output

If we execute the above query, the result is produced as follows −

+------------+
| FIRST_DAY  |
+------------+
| 5          |
+------------+

Example

The setting of language impacts the character string interpretation as SQL Server converts those strings to date values for database storage.

• It impacts the display of date values stored in the database.
• It does not impact the storage format of date data.

Here, we are trying to set the language to Italian using the following query −

SQL> SET LANGUAGE Italian;  
SELECT @@DATEFIRST AS RESULT;  

Output

When we execute the above query, the output is obtained as follows −

+----------+
| RESULT   |
+----------+
| 1        |
+----------+

Example

Here, we are trying to set the language to us_english using the following query −

SQL> SET LANGUAGE us_english;  
SELECT @@DATEFIRST AS RESULT;  

Output

On executing the above query, the output is displayed as follows −

+---------+
| RESULT  |
+---------+
| 7       |
+---------+

Example

Here, we are trying to set the first day of the week value to 7 i.e. Sunday, and assume that the current day, TODAY falls on Tuesday.

The below SELECT statement returns the DATEFIRST value and the number of the current day of the week.

SQL> SET DATEFIRST 7;
SELECT @@DATEFIRST AS 'FIRST DAY';

SELECT GETDATE() as 'TODAY_DATE', DATEPART(dw, SYSDATETIME()) AS 'TODAY';

Output

If we execute the program, the result is produced as follows −

+-------------+
| FIRST_DAY   |
+-------------+
| 7           |
+-------------+

+---------------------------+---------+
| TODAY_DATE                | TODAY   |
+---------------------------+---------+
| 2023-02-21 14:07:42.010   | 3       |
+---------------------------+---------+