SET DATEFIRST Statement and @@DATEFIRST Function in SQL Server(TSQL)

dbtut February 17, 2019 MSSQL, TSQL

We can set the first day of the week for a session with SET DATEFIRST Statement. We can query the first day of the week for a session with the @@DATEFIRST Function.

First, by opening a session, let’s query the first day of the week with the @@DATEFIRST function.

SELECT @@DATEFIRST

1	SELECT @@DATEFIRST

For a more meaningful result instead of 7, let’s change the query as follows.

SELECT CASE
   WHEN @@DATEFIRST=1 THEN 'Monday'
   WHEN @@DATEFIRST=2 THEN 'Tuesday'
   WHEN @@DATEFIRST=3 THEN 'Wednesday'
   WHEN @@DATEFIRST=4 THEN 'Thursday'
   WHEN @@DATEFIRST=5 THEN 'Friday'
   WHEN @@DATEFIRST=6 THEN 'Saturday'
   WHEN @@DATEFIRST=7 THEN 'Sunday'
END AS FirstDayOfWeek

SELECT CASE

WHEN @@DATEFIRST=1 THEN 'Monday'

WHEN @@DATEFIRST=2 THEN 'Tuesday'

WHEN @@DATEFIRST=3 THEN 'Wednesday'

WHEN @@DATEFIRST=4 THEN 'Thursday'

WHEN @@DATEFIRST=5 THEN 'Friday'

WHEN @@DATEFIRST=6 THEN 'Saturday'

WHEN @@DATEFIRST=7 THEN 'Sunday'

END AS FirstDayOfWeek

Now let’s set the first day of the week to 1 and run the same query.

SET DATEFIRST 1
GO
SELECT CASE
   WHEN @@DATEFIRST=1 THEN 'Monday'
   WHEN @@DATEFIRST=2 THEN 'Tuesday'
   WHEN @@DATEFIRST=3 THEN 'Wednesday'
   WHEN @@DATEFIRST=4 THEN 'Thursday'
   WHEN @@DATEFIRST=5 THEN 'Friday'
   WHEN @@DATEFIRST=6 THEN 'Saturday'
   WHEN @@DATEFIRST=7 THEN 'Sunday'
END AS FirstDayOfWeek

SET DATEFIRST 1