@@DATEFIRST on SQL Azure Managed Instance

SQL Server Logo

The @@DATEFIRST returns the current value of SET DATEFIRST for a specific session.  By default, for all U.S English environments this value is 7.  In simple terms this value dictates what should be the starting day as shown below:

 

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

 

The reason why I am writing this post is I wanted to help others that maybe having some oddities with the output of their dates, as an example we had an issue where by the dates that where being inserted into a table by executing a stored procedure which should show the date of a given Friday or Saturday from a scheduled job where showing a day less then what they should have been, meaning that February 7th 2020 which is a Friday was showing as February 6th 2020 which is a Thursday.

Below is a snippet of code to show the area of the stored procedure where this date was being calculated.

 

 

The platform that we are using is Managed SQL Server from Azure.  I got down to debugging to work out why this was happening, I confirmed that the user that was running the stored procedure was configured for British culture.  I then checked to make use the session that I was connected to was outputting 1 after executing the below:

 

SELECT @@DATEFIRST

This indeed did show the correct result of 1 being Monday. The next step was to check what the output of @@DATEFIRST was when running from a context of a scheduled job.  I created a simple job with 1 job step with the following T-SQL:

PRINT ‘The Value of @@DATEFIRST is => ’ + CAST(@@DATEFIRST AS NVARCHAR(2))

I then proceeded to execute this Job, once completed I checked the Job history and viewed the Log file for this job.  The output of @@DATEFIRST was showing as 7 being Sunday.  This was why our Dates were slightly off by one.

To resolve this within the stored procedure in question at the beginning I added the following:

SET DATEFIRST 1;

After adding this the dates were showing correctly.

 

Leave a Reply

Your email address will not be published. Required fields are marked *