Interview Question of the Week #036 – What is the difference between @@DATEFIRST and SET DATEFIRST?

September 13, 2015

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

Question: What is the difference between @@DATEFIRST and SET DATEFIRST with respect to SQL Server?

Answer: SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as the first day of the week using

SET DATEFIRST 5

This will set Friday as the first day of the week.
@@DATEFIRST returns the current value, for the session, of SET DATEFIRST.

SET LANGUAGE italian
GO
SELECT @@DATEFIRST
GO
----This will return result as 1(Monday)
SET LANGUAGE us_english
GO
SELECT @@DATEFIRST
GO
----This will return result as 7(Sunday)

In this way @@DATEFIRST and SET DATEFIRST are related. When I learned about this feature I was very glad as our company has started to server global clients and simple feature like this helps a lot to avoid confusion.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime

SQL SERVER – Who Dropped Table or Database?

SQL SERVER – ERROR: Autogrow of file ‘MyDB_log’ in database ‘MyDB’ was cancelled by user or timed out after 30121 milliseconds

SQL SERVER – Interesting Function AGENT_DATETIME

March 13, 2015

SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ‘80040e07’ or Microsoft SQL Native Client error ‘80040e07’

December 3, 2012

SQL SERVER – System Function @@IDLE to Find System Ideal Time

November 26, 2016

2 Comments. Leave new

Maulesh Mevada
September 13, 2015 8:03 am
I would like to know set DATEFIRST is database level or session level, because in some case we want user specific start day, in this case if session level is ok but when at database level its create trouble.
Reply
vivekjamshedpur
September 14, 2015 12:05 pm
You can set it at run time and reset it again to original value.
Reply