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

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)

Previous Post
SQL SERVER – Who Dropped Table or Database?
Next Post
SQL SERVER – ERROR: Autogrow of file ‘MyDB_log’ in database ‘MyDB’ was cancelled by user or timed out after 30121 milliseconds

Related Posts

2 Comments. Leave new

  • 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
  • You can set it at run time and reset it again to original value.

    Reply

Leave a Reply

Menu