SSRS DateAdd Expression failing.

InspiredUser33157 216 Reputation points
2021-03-12T14:07:22.24+00:00

Anyone have any insight on why the first line will work fine in SQL Server and the second wont in ssrs expression builder? I added the "DateValue("1/1/1900")" to the second option below because the ssrs can't seem to interpret the "0" for the StartDate parameter. The goal with this expression is to find the date of the Monday of each week of a given month. The below was modeled from https://stackoverflow.com/questions/28100992/ssrs-expression-find-first-day-of-week-from-week-value

DECLARE @MyDate DATETIME = '9/16/2020' --GETDATE() SELECT DatePart(Day, DATEADD(WK, DATEDIFF(wk, 0, '1/1/' + Cast(DATEPART(YEAR, @MyDate ) as varchar)) + (DatePart(wk,@MyDate)-1), 6)) AS StartOfWeek;

=DateAdd(DateInterval.WeekOfYear, 6, DATEDIFF(DateInterval.Weekday, DateValue("1/1/1900"), DateValue("1/1/" + CStr(DATEPART(DateInterval.Year, Fields!ReadingTime.Value)) )) + (DatePart(DateInterval.WeekOfYear,Fields!ReadingTime.Value)-1))

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
0 comments
Accepted answer
  1. InspiredUser33157 216 Reputation points
    2021-03-16T15:17:10.603+00:00

    With more research, I was able to find a expression to satisfy the requirement.

    =DatePart(DateInterval.Day,DateAdd("d",3-DatePart("w",(DateAdd(DateInterval.WeekOfYear, Fields!WeekMonth.Value - 1, CDate("1/1/" & Today.Year.ToString())))),(DateAdd(DateInterval.WeekOfYear, Fields!WeekMonth.Value - 1, CDate("1/1/" & Today.Year.ToString())))))

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-03-15T08:41:11.897+00:00

    Hi,

    Sorry that I could not understand the requirement very clearly.

    The goal with this expression is to find the date of the Monday of each week of a given month.

    Are you trying the find the every particular Monday for each row of ReadingTime field?

    I tried to read the expression code, I assume the first DateAdd expression could be wrong ?(not sure here since I could not related it with requirement confidently)

    As in =DateAdd(DateInterval.WeekOfYear,...) , the DataInterval.WeekOfYear returns only the week number from 1-53, so I think it is not supposed to use in DateAdd.
    Tell me what you think, I would try to help further :)

    Best Regards,
    Joy

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our [documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.