Search code examples
reporting-servicesssrs-2008-r2isoweek-numberdateadd

SSRS Returning incorrect WW

I have an SSRS report (2008R2 3.0) that uses parameter filters for workweek in the format YYYYWW. In 2018 there were 52 work weeks based on the ISO standard (source) .

For default parameter value I select the last WW using the following formula:

DATEPART(DateInterval.Year, DATEADD("d", -7, now())) & FORMAT(DATEPART(DateInterval.WeekOfYear, DATEADD("d", -7, now())), "00")

For some odd reason this returned 201853. From what I understand it should be 201852 given the date when ran was 20180106.

I know in T-SQL I can define return type using ISO_WEEK but is there an equivalent for DateInterval in SSRS?

To simplify:

DATEPART(DateInterval.WeekOfYear, DATEADD("d",-7,now()))

Returns 53 instead of 52 when ran from 201801-201807.

Any idea as to why this is returning the wrong WeekOfYear value?

Solution

  • I think this may be due to your Regional First Day of the Week setting on your server.

    enter image description here

    If your First Day of the Week is Saturday, there are 53 weeks in the 2018. You can set the first day of the week with the DATEFIRST setting in SQL Server.

    Use SELECT @@DATEFIRST to determine your servers setting.

    This example code will show the difference.

    SET DATEFIRST 7
SELECT @@DATEFIRST
SELECT DATEPART(WEEK, '2018-12-30')
SET DATEFIRST 1
SELECT @@DATEFIRST
SELECT DATEPART(WEEK, '2018-12-30')

    enter image description here

    For your query, you may need to set the first day of the week to Sunday with SET DATEFIRST 1.

    MS Docs: DATEFIRST