How to use current_date function and set the report run at a particular time each day

Question

Requirement: I want to query for bugs filed within 24 hours. Immaterial of when it is run on a particular day, it should take the current_date and still take the time as 8:45AM all the time. How to achieve this?

I tried

(CAST("Bug Header"."Filed Date"  AS TIMESTAMP) >=  TIMESTAMPADD(SQL_TSI_HOUR, -HOUR(CURRENT_DATE)+8, CURRENT_DATE-1) )

but this fails stating

State: HY000. Code: 22025. [nQSError: 22025] Function TimestampAdd is called with an incompatible type.

Please have your System Administrator look at the log for more details on this error. (HY000)

Kindly help.

Gianni Ceresa · Answer

If you got what you were looking for you can maybe close the thread? It will help others looking for the same or similar things. Right now it's still This question is Not Answered.

Vpandura-Oracle · Answer

Thank you very much! It was a great help.

Gianni Ceresa · Answer

CURRENT_DATE = SYSDATE, so doing CURRENT_DATE-2 and CURRENT_DATE-3 you aren't getting there ...

Stop thinking database, OBIEE is database agnostic, so use the OBIEE functions for the calculations on dates to have a chance it keeps working the day your source change and doesn't get anymore +/- on a date.

SYSDATE 08:45 = TIMESTAMPADD(SQL_TSI_MINUTE, 525, CAST(CURRENT_DATE as timestamp))

SYSDATE-1 08:45 = TIMESTAMPADD(SQL_TSI_DAY, -1, TIMESTAMPADD(SQL_TSI_MINUTE, 525, CAST(CURRENT_DATE as timestamp)))

And if you only get zero get rid of the SUM and output all the lines and debug your formula there, line by line.

You maybe also want to use that as filter, because it's useless to ask your DB to return all the bugs if you only want to know the bugs between SYSDATE-1 and SYSDATA 08:45.