Post reply

Calculated Default Date Parameter

  • August 8, 2007 at 6:04 pm

    #175825

    I need to set a default parameter on an RS report to the date for the most current week end date which is Thursday on our system.  I can use this to get the current date minus so many days:

     SELECT CAST(FLOOR(CAST(GETDATE()-6 AS FLOAT)) AS DATETIME)

    But what I need to determine is how many days to subtract from today's date to get last Thursday's date (if today isn't Thursday).  I thought this should work but I've been trying a number of mutations and haven't gotten anywhere. 

    If anyone has any insight on how I can get this to work, it would be greatly appreciated!

    case when date_format(getdate(),"%w")=4 then getdate()

    case when date_format(getdate(),"%w")=3 then getdate()-6

    case when date_format(getdate(),"%w")=2 then getdate()-5

    case when date_format(getdate(),"%w")=1 then getdate()-4

    case when date_format(getdate(),"%w")=0 then getdate()-3

    case when date_format(getdate(),"%w")=6 then getdate()-2

    case when date_format(getdate(),"%w")=5 then getdate()-1

    I get either date_format is not a recognized function name or date_format not declared (when I tried to set up non-queried and use an iif).

    Thanks,

    Beth

     

  • August 8, 2007 at 6:50 pm

    #725567

    I believe this will do.

    SET DATEFIRST 7 --Default for SQL Server

    SELECT GETDATE()-DATEPART(dw,GETDATE())-2

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 9, 2007 at 10:21 am

    #725803

    Thanks for the quick response Jeff.  This works perfectly for Sun-Thurs.  Fri and Sat return the previous week (ie if I run it tomorrow, the end date is still 8/2).  I'm hoping that will not be an issue but you know how users can be. Anyway, since we have a number of systems that operate Fri-Thurs weeks, I will definitely find plenty of uses for it.  Thanks again!

  • August 13, 2007 at 8:02 am

    #726444

    Try modifying the select to the following:

    SELECT GETDATE()-(DATEPART(dw,GETDATE())+2) %  7

    This should return the correct Thursday for both Friday and Saturday.

     

Viewing 4 posts - 1 through 4 (of 4 total)

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