Filtering report data based on the time on or before the current date at either noon or midnight

Dave Kekish

I have a small summary report of a few columns to make sure that the PVO data we extract for the SubledgerJournalDistributionExtract PVO matches up to what is in oracle. Due to the scheduling of both reporting and PVO processing, I really only want values from either midnight or noon of the current date.

We run PVO extractions for this table at 12 and 12, and would like to run the report at 12 and 12. Since with the Oracle Scheduler, my request to run at noon may actually be 12:05, 1:15, etc I really only want to look at data from the scheduled time in the report.

For SQL Server (my destination system) I have this

SELECT CASE WHEN (DATEPART(HOUR, GETUTCDATE()) >= 12) THEN 

DATEADD(HOUR, 12, CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME2))

ELSE 

CAST(sysutcdatetime() as date) 

END AS LastModifiedDate

But I'm struggling to get an Oracle version that is even remotely close. For example, I can't select SYSDate for example. I assume there has to be a way to do this fairly simple filter check.

Does anyone have an idea or a direction to point me in?

Thanks

Dave Kekish

Just on the off chance that someone, somewhere will come across this and go, yes, thats what I need too...

Here is what I eventually came up with

WHERE LAST_UPDATE_DATE < CASE WHEN extract(hour from current_timestamp) >= 12 THEN 

(TRUNC(Current_Date) + interval '12' hour)

else 

TRUNC(Current_Date) END

your milage may vary.