question

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 asked ChuckSalerno-4276 answered

SSRS DateAdd Expression failing.

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered ChuckSalerno-4276 commented

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.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If I provide a date value, I need to find the Monday of that week for the datevalue provided.

The first script I provided will in fact give me exactly what I need but I can't seem to translate that into SSRS expression. I cannot simply add the script to the sql statement as I amy working with OSI PI driver which does not support the date functions such as DateAdd and DateDiff. Hope that helps.

0 Votes 0 ·