Incorporating Current Date into Query.

[Former Member]

I have been asked to replace the start and end dates in my report to the (current date - 45 days) as the start date and (current date - 90 days) as the end date.  I have pasted the first part of my query below.  The line I have highlighted is the part I assume I need to change.  Can anyone help?

with accts as (

select rmsfilenum,rmstrandte,

                  case when hstnewvalu in ('L18','P18') then 'daterqst'       

            when hstoldvalu in ('L18','P18') and hstnewvalu in ('L1A','P1A')  

                             then 'dateappr'

            when hstoldvalu in ('L18','P18') and not (hstnewvalu) in ('L1R','P1R')  

                                   then 'daterjct' end as datedata            

         from reporting.rhistfl

where rmstrandte between  {?strtdt} and {?Enddt}  

and rmstrancde = 'MT'                                        

and rmsfldimpl = 'RMSSTATUS'

and (hstnewvalu in ('L18','P18')                                              

    or hstoldvalu in ('L18','P18')  )    ),                                     

[former_member292966 · Accepted solution]

Hi Dee,

The exact syntax will depend on your database.  If you are using SQL Server your query would look something like:

where rmstrandte between dateadd (d, -45, GETDATE()) and dateadd (d, -90, GETDATE())

The dateadd function should give you the dates you want.

Good luck,

Brian

[Former Member · Accepted solution]

Brian,

Thanks for the help.  I really appreciate it.  One more question, though.  When I used the dateadd and getdate line you used above, I got the error below.  Any idea why I am getting this error?

[former_member183750 · Accepted solution]

Vendor Code errors are being passed from the database client through the report engine unmodified. E.g.; search your database documentation for the error, of google the error. One of many hits I get when I google 'Vendor code 204 DB 2' is:

IBM "DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704" when connecting to new Controlle...

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[former_member292966 · Accepted solution]

Hi Dee,

For DB2, use Current Date instead of GetDate().  As I mentioned before, the syntax I used is for SQL Server.

where rmstrandte between (Current Date -  45 days) And (Current Date - 90 days)

Good luck,

Brian

[Former Member]

Ludek and Brian,

Thanks for the information and the link.  I appreciate the help.  And thanks again for responding to my post.