[SOLVED] DATEPART: specifying first day of week when SET DATEFIRST cannot be used

DATEPART: specifying first day of week when SET DATEFIRST cannot be used

I am creating some reports in ReportServer (dynamic list type), one of them is retrieving info from SQL Server for events in the past week.

In SSMS, it's simple enough, I get the needed info (timewise) with

... WHERE DATEPART(week, event_date) = DATEPART(week, DATEADD(d, -5, GETDATE()))

preceded by a

SET DATEFIRST 1;

statement so that Monday is set as the first day of the week.

Unfortunately, ReportServer does not accept the SET DATEFIRST statement as part of the query, so after leaving it out, the data returned ranges from Sunday to Saturday instead of Monday to Sunday. I cannot make global changes to the database (or server, for that matter).

How can I get round this?

Solution

You can try something along this using @@DATEFIRST. This is a system variable you can use without any declaring or setting in inline/ad-hoc queries:

--This is a Sunday

DECLARE @OneSunday DATE='20190929';

--Your statement returns differently in different cultures

SET LANGUAGE ENGLISH;
SELECT DATEPART(week, @OneSunday); --<-- returns 40
SELECT @@DATEFIRST;                --<-- returns 7  

SET LANGUAGE GERMAN;
SELECT DATEPART(week, @OneSunday); --<-- returns 39
SELECT @@DATEFIRST;                --<-- returns 1

As you can see, the system variable @@DATEFIRST reflects the day's index, which is set by the culture. And you can use it for some correctional arithmetics.

SET LANGUAGE ENGLISH;
SELECT DATEPART(week, DATEADD(DAY,(@@DATEFIRST % 7),@OneSunday));

SET LANGUAGE GERMAN;
SELECT DATEPART(week, DATEADD(DAY,(@@DATEFIRST % 7),@OneSunday));

Now both return 40.

I use % 7 to get a 0, where @@DATEFIRST returns the 7.