Post reply

Rolling week expression

  • July 12, 2011 at 10:29 am

    #239428

    I need a rolling date to go from Sunday to Sunday, but for 3 weeks back.

    I believe the SQL would look like this:

    --DateStart

    SELECT DATEADD(wk, DATEDIFF(wk, 6, DATEADD(wk, -3, GETDATE())), 6)

    --DateEnd

    SELECT DATEADD(wk, DATEDIFF(wk, 6, DATEADD(wk, -2, GETDATE())), 6)

    I would like to use an expression instead. I have tried this as the DateStart:

    DateAdd(DateInterval.WeekOfYear, DATEDIFF(DateInterval.WeekOfYear, 6, DATEADD(DateInterval.WeekOfYear, -3, Today())), 6)

    But I get a error message:

    "The DefaultValue expression for the report parameter 'DateStart' contains an error: Argument 'Date1' cannot be converted to type 'Date'.

    Any help is appreciated.

  • July 13, 2011 at 7:01 am

    #1352127

    Using your SQL, in SSRS:

    --DateStart

    SELECT DATEADD("ww", DATEDIFF("ww", 6, DATEADD("ww", -3, GETDATE())), 6)

    --DateEnd

    SELECT DATEADD("ww", DATEDIFF("ww", 6, DATEADD("ww", -2, GETDATE())), 6)

  • July 13, 2011 at 7:26 am

    #1352147

    Daniel Bowlin (7/13/2011)

    Using your SQL, in SSRS:

    --DateStart

    SELECT DATEADD("ww", DATEDIFF("ww", 6, DATEADD("ww", -3, GETDATE())), 6)

    --DateEnd

    SELECT DATEADD("ww", DATEDIFF("ww", 6, DATEADD("ww", -2, GETDATE())), 6)

    Thanks for your response Daniel.

    "ww" is the same thing as DateInterval.WeekOfYear however I did try it and received the same error. Also, there is not a GETDATE() in SSRS...just NOW() and TODAY().

    Thank you for your suggestion, however my expression is already what you are implying.

  • July 13, 2011 at 10:35 am

    #1352287

    Oops....differences between TSQL & SSRS expressions.

    Here try this

    --DateStart

    =DATEADD("ww",-3,DATEADD("d",-(WeekDay(Today())) + 1, Today()))

    --DateEnd

    =DATEADD("ww",-3,DATEADD("d",-(WeekDay(Today())) + 1, Today()))

  • July 13, 2011 at 11:04 am

    #1352315

    Daniel Bowlin (7/13/2011)

    Oops....differences between TSQL & SSRS expressions.

    Here try this

    --DateStart

    =DATEADD("ww",-3,DATEADD("d",-(WeekDay(Today())) + 1, Today()))

    --DateEnd

    =DATEADD("ww",-3,DATEADD("d",-(WeekDay(Today())) + 1, Today()))

    That did it Daniel! Expression logic seems quirky to me.

    Just FYI, date end should be:

    --DateEnd

    =DATEADD("ww",-2,DATEADD("d",-(WeekDay(Today())) + 1, Today()))

    I know you just forgot to change the -3 but wanted to point it out in case this helped someone else.

    Thanks again for your assistance and time!

  • July 13, 2011 at 12:23 pm

    #1352358

    Right you are, I did forget to change it to -2.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply