SSRS DateAdd Expression failing.

InspiredUser33157 211 Reputation points

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

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,372 questions
0 comments No comments
{count} votes

Accepted answer
  1. InspiredUser33157 211 Reputation points

    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 No comments

1 additional answer

Sort by: Most helpful
  1. Joyzhao-MSFT 15,471 Reputation points Microsoft Employee


    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,

    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.