SQL: Calculating Day of the Week Reliably in T-SQL

Some T-SQL functions like DATEPART work differently based upon your session settings. If you want to write reliable code, you should always check for these issues, and try to write code that’s impervious to these settings.

Let’s take finding the day of the week as an example. It might seem that to find out if a day is a Sunday, you could just do this:

DECLARE @DayToCheck date = ‘20190224’;

SELECT DATEPART(weekday, @DayToCheck);

By default, it returns the value 1 as expected. But it only does that if the DATEFIRST setting in the session was 7.

If I had changed the DATEFIRST value in my session like this:

SET DATEFIRST 2;

Then that same code would return 6 instead. What we want is to get the desired output, regardless of the current session settings. The way to do this is instead to pick a date that we know was say a Saturday, like 6th January 1900. Then if we just work out the number of days from that date to our calculated date, modulo 7, we’ll end up with 1 for Sunday, 2 for Monday, etc.

You can see the output in the main image above. And no matter what you change DATEFIRST to, the output doesn’t change.

2019-02-15