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

Question

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?

The simple answer would be to deploy stored procedures that contain the `SET DATEFIRST` change. Can you coax your DBAs to allow this? Be aware that `SET DATEFIRST` is scoped to the current session so you may also want to save the values of `@@DATEFIRST` before changing it and restore the value after your query - otherwise you have the potential to affect other queries reusing the same connection. — AlwaysLearning, Sep 30 '19 at 07:05
Thanks @AlwaysLearning but that's part of my problem: I cannot make any changes to the database unless they're really process-related; I'm afraid reporting does not fall into that category — , Sep 30 '19 at 07:08
The best idea might be to introduce a physical number/date table, which is very handsome in many places. Something along [this example](https://stackoverflow.com/a/32474751/5089204) — Shnugo, Sep 30 '19 at 07:11

Dale K · Answer 1 · 2019-09-30T08:11:22.750

1

Calculate the desired first day of the week and then use a week date range in your where clause e.g.

declare @FirstDayOfWeek date, @Now date = getdate();

select @FirstDayOfWeek = dateadd(day, -1*(case when datepart(weekday, @Now) > 1 then datepart(weekday, @Now)-2 else 6 end), @Now);

select *
from dbo.MyTable
where event_date >= @FirstDayOfWeek and event_date < dateadd(week, 1, @FirstDayOfWeek);

And of course you can also embed the calculation in the query e.g.

select *
from dbo.MyTable
cross join (
  select dateadd(day, -1*(case when datepart(weekday, getdate()) > 1 then datepart(weekday, getdate())-2 else 6 end), getdate()) FirstDayOfWeek
) D
where event_date >= D.FirstDayOfWeek and event_date < dateadd(week, 1, D.FirstDayOfWeek);

Note: You could just duplicate the date calculation into the where clause and avoid the join but I dislike having duplicated calculations.

edited Sep 30 '19 at 08:11

answered Sep 30 '19 at 07:07

Dale K

25,246
15
42
71

Thank you @Dale Burrell but without even trying, I can tell you that Reportserver won't allow me to DECLARE anything, no more than it allows me to SET anything – Sep 30 '19 at 07:11
@ChrisBE are you sure? `declare` is very very different to `set` in terms of permissions. I would give it a try. It works in SSRS. – Dale K Sep 30 '19 at 07:13
1

@ChrisBE and of course you don't need to declare it, you can compute it as part of the query. – Dale K Sep 30 '19 at 07:21
2

@ChrisBE, Just to mention this: You can simulate a declared variable using a CTE: `WITH myVariables(VariableA) AS(SELECT 'names') SELECT * FROM myVariables v CROSS JOIN sys.objects o WHERE o.[name] like '%' + v.VariableA + '%'`. This will use `'names'` similar to a variable... – Shnugo Sep 30 '19 at 07:38

Shnugo · Accepted Answer · 2019-09-30T07:24:52.067

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.

thank you! You've put me on the right path. I modified your suggestion to fit my query and the following works perfectly: `DATEPART(week, DATEADD(d, CASE WHEN @@DATEFIRST % 7 = 0 THEN -1 ELSE 0 END , event_date)) = DATEPART(week, DATEADD(d, -5, GETDATE()))` — , Sep 30 '19 at 07:35
@ChrisBE I'm glad to help you. Just one tiny hint: In your construction you don't need the `%7`. You can test for `@@DATEFIRST=7`. The trick with `%` makes it easier in computations... — Shnugo, Sep 30 '19 at 07:45

Squirrel · Answer 3 · 2019-09-30T07:21:40.923

Since you wanted week that starts with Monday, you use a reference date that is Monday like 1900-01-01 and calculate the start and end date of a week

begin_of_week = dateadd(day, datediff(day, '1900-01-01', [date]) / 7 * 7, '1900-01-01')

and to get the end of week date,

end_of_week = dateadd(day, datediff(day, '1900-01-01', [date]) / 7 * 7 + 7, '1989-12-31')

so put that into your query ( i am assuming that your event_date may contain time component, hence, the upper condition is a less than < )

WHERE event_date >= dateadd(day, datediff(day, '1900-01-01', getdate()) / 7 * 7, '1900-01-01')
AND   event_date <  dateadd(day, datediff(day, '1900-01-01', getdate()) / 7 * 7 + 7, '1900-01-01')

There is [`@@DATEFIRST`](https://learn.microsoft.com/en-us/sql/t-sql/functions/datefirst-transact-sql?view=sql-server-2017) for this... — Shnugo, Sep 30 '19 at 07:27

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

3 Answers3