April 23, 2015 at 1:41 pm
Hi am trying to come up with an expression to get start & end of particular week by providing week number but get i,proper results.
=DateAdd(DateInterval.WeekOfYear, Fields!WeekNumber.Value - 1, CDate("1/1/" & Today.Year.ToString()))
This is the exp I am using by passing week 22
But I get May 28 .Where as It should be may 25 and ending date should be 31.
Can anyone advise pls
April 23, 2015 at 5:27 pm
By far the easiest way of dealing with stuff like this is by using a Calendar table. Then you can get ANY day of ANY week...
SELECT ..
FROM CalendarTable
WHERE WeekNumber = @WeekNumber
AND DayName = 'Friday';
All you would need to go is get the database guys to create a calendar table or function (that returns a table) for you.
April 26, 2015 at 10:17 pm
Pietlinden is right, a date table is the best and easiest. That's what we have at work. But until then, you can use a code mashup by adding to what you already have. It's not pretty, but it works! 😀
There are a million ways to do things in Report Builder, but this is just what comes to mind right now.
Also, the reason yours isn't working completely is because January 1, 2015 starts on a Thursday, so the code below just looks at the day of the week and changes it to Monday or Sunday.
This will work if you are just querying your calendar week of the year and it also looks like your "week" is Monday through Sunday. try this:
Date for starting Monday:
'----=DateAdd("d",2-DatePart("w",(DateAdd(DateInterval.WeekOfYear, Fields!WeekNumber.Value - 1, CDate("1/1/" & Today.Year.ToString())))),(DateAdd(DateInterval.WeekOfYear, Fields!WeekNumber.Value - 1, CDate("1/1/" & Today.Year.ToString()))))
Date for ending Sunday:
'----=DateAdd("d",8-DatePart("w",(DateAdd(DateInterval.WeekOfYear, Fields!WeekNumber.Value - 1, CDate("1/1/" & Today.Year.ToString())))),(DateAdd(DateInterval.WeekOfYear, Fields!WeekNumber.Value - 1, CDate("1/1/" & Today.Year.ToString()))))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply