Interview Question of the Week #046 – How @@DATEFIRST and SET DATEFIRST Are Related?

Question: How @@DATEFIRST and SET DATEFIRST are related?

Answer: The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as 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 Scripts
Previous Post
SQL SERVER – Inserting into ColumnSet with SPARSE Columns
Next Post
SQL SERVER – New features in SQL Server 2016 Setup Wizard

Related Posts

1 Comment. Leave new

  • The ISO-8601 standards already have a 1-7 ordering of the week that ends with Sunday=7, but Microsoft encourages local dialects. This is always a bad idea.

    Reply

Leave a Reply