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

Vpandura-Oracle

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

Hi,

Would help if next time you also provide some context and at least the tool you talk about.

I assume it is OBIEE ...

TIMESTAMPADD(SQL_TSI_MINUTE, 525, CAST(CURRENT_DATE as timestamp))

That's 8h45 AM of the current day.

525 = 60*8 + 45

CURRENT_DATE doesn't have any time component, it's only the date

CURRENT_TIME(0) would give the time component (without the date)

Vpandura-Oracle

Thank you so much, Gianni Ceresa!

It helped at least the report gets shown. Firstly, yes it is OBIEE report.

Actually I want the report to be run to find the inflow of bugs filed between SYSDATE at 8:45AM and SYSDATE -1 8:45AM.

SUM(CASE WHEN ((cast("Bug Header"."Filed Date" as TIMESTAMP) BETWEEN TIMESTAMPADD(SQL_TSI_MINUTE, 525, CAST(CURRENT_DATE-2 as timestamp)) AND TIMESTAMPADD(SQL_TSI_MINUTE, 525, CAST(CURRENT_DATE-3 as timestamp)) ) ) then 1 else 0 END)

I get ZEROs and so I am not sure whether it really returns the correct answer. Please let me know whether above query will fetch me the requirement I outlined above.

Regards
-vinod

Gianni Ceresa

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.

Vpandura-Oracle

Thank you very much! It was a great help.

Gianni Ceresa

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.