Skip to content
Advertisement

SQL – how to filter by timestamp looking for anything after a certain time on a previous day

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?

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.