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.
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 SQL Accounts for both environments had a different language setting (regional settings of the SQL Servers where the same).
DATEFIRST sets the first day-of-week:
7 = Sunday (Default setting, US)
1 = Monday (European setting)
After a few hours, I finally resolved it:
IF (@@DATEFIRST != 1)
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).