Oracle date calculations trunc(sysdate)

Question

I need to qualify a query from a Database on Oracle for a report based on certain date ranges

1st query

5pm-7am (Between 5pm yesterday and Today 7am)

2nd query

for 7am-5pm ((Between 5pm yesterday , Today 7am)

How would I do this??? I need to know Oracle Syntax to specify those date criterias

Thanks in Advance

Solution

I would probably do something like

WHERE some_date_col BETWEEN trunc(sysdate-1) + interval '17' hour -- 5pm yesterday
                        AND trunc(sysdate) + interval '7' hour -- 7am today

and

WHERE some_date_col BETWEEN trunc(sysdate) + interval '7' hour -- 7am today
                        AND trunc(sysdate) + interval '17' hour -- 5pm today

There is another thread on adding hours and minutes to dates that goes into more detail about different ways of specifying date offsets.