I need to filter a query results to only bring in rows of data that were created after 5pm the previous BUSINESS DAY up until current time, with business day being Monday to friday. I can filter for previous business day, and I can filter by timestamp…but I can’t combine the 2 in order to filter after 5pm previous business day.
So for timestamp filter the field is call ‘create_ts’ and I can filter for after 5pm like so: AND to_char(create_ts, ‘HH24:MI:SS’) >= ’17:00:00′
And for previous business day I can filter like so: and a.create_ts >= trunc(prevbd(sysdate))
how do I filter a.create_ts for >= trunc(prevbd(sysdate) after 5pm?
Advertisement
Answer
If prevbd is a function you’ve defined that returns a date from the previous business day (I’m guessing from the fact that you’re calling trunc that it returns a date that is the current time on the previous business day which seems like a weird design choice), then just
a.create_ts >= trunc( prevbd(sysdate) ) + interval '17' hour
to look for things after 5pm on the previous business day.