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))