Date Help

[pungigis]

Hi, I am pretty new to sql and need help with the following,

I have a report that currently runs with the following where statement...

TIME-Admission.FULLDATE BETWEEN ADD_MONTHS(@variable('Enter Full Date:'),-6) AND @variable('Enter Full Date:')

I need to be able to duplicate this but have it run so that I can automate it, like run on current date -6 thru current date. In essence what I want to do is schedule the report to run on 11-15 for May thru Oct data.

Any help would be appreciated.

Thanks

 

[Andrzejek]

You can get the current date from DB this way:

[blue]SELECT SYSDATE FROM dual[/blue]

So now you can substitute your dates in your SQL:

TIME-Admission.FULLDATE BETWEEN ADD_MONTHS(([blue]SELECT SYSDATE FROM dual[/blue]), -6) AND ([blue]SELECT SYSDATE FROM dual[/blue])

Have fun.

---- Andy

 

[lewisp]

To continue with Andrzejeks answer, SYSDATE is a pseudo-column so you can include it directly in the query:

WHERE TIME-Admission.FULLDATE BETWEEN ADD_MONTHS(SYSDATE,-6) AND SYSDATE