DATEFIRST within a function

If you want to calculate the day-of-week for your DateTime values, say for a Service Window/Time, it’s easy to use the function:

But, it’s tricky since the DATEFIRST setting of your current SQL Session depends on the user-account your currently using. The worst thing is, you can’t use the SET DATEFIRST statement within a function…it’s the language setting of the SQL Account you wish to tackle.

An example:
During my last assignment, I wrote a SP that was using a function (UDF) called ‘HandledTime’. HandledTime was the duration (in minutes or hours) that it took to resolve an incident. To make it short, I used day-of-week to calculate two things:
– Number of full days between the datetime’s @Received and @Handled
– Check whether both datetime’s didn’t fell in the weekend (since the service window started from Monday untill Friday)

My calculations worked fine on the development environment, but when we deployed it to the production environment it all got messed up.

The cause:
The SQL Accounts for both environments had a different language setting (regional settings of the SQL Servers where the same).

The solution:
DATEFIRST sets the first day-of-week:
7 = Sunday (Default setting, US)
1 = Monday (European setting)

After a few hours, I finally resolved it:

SET @StartDate = ((DATEPART(dw,@StartDate) + @@DATEFIRST) %7) -1
SET @EndDate = ((DATEPART(dw,@EndDate) + @@DATEFIRST) %7) -1
SET @StartDate = DATEPART(dw,@StartDate)
SET @EndDate = DATEPART(dw,@EndDate)

This solution works every time if want to use the European standard (Monday is the first day of week).

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s