3

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

APC
  • 144,005
  • 19
  • 170
  • 281
rgvwed
  • 47
  • 1
  • 4
  • 11

1 Answers1

6

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.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • hi thanks, actually I am probably going to do to separate reports, so first query would be DISCH_DT_TM BETWEEN pi_to_gmt(TRUNC(SYSDATE) - ???,( pi_time_zone(2) )) AND SYSDATE ??? /* this for the – rgvwed Aug 21 '12 at 23:34
  • @rgvwed - I'm not sure what `pi_to_gmt` is or what `pi_time_zone(2)` means. I'm assuming those are functions in your system whose behavior you understand. You can use each of the `WHERE` clauses I posted in a separate report-- just add whatever calls to your functions are needed based on whatever requirements you have. – Justin Cave Aug 21 '12 at 23:44
  • oK I will test the interval on my database and see if works, how exactly does that work? are there other ways to do this – rgvwed Aug 21 '12 at 23:46
  • what does trunc and intervals actually do? do it reset current day? – rgvwed Aug 22 '12 at 04:37
  • @rgvwed - `trunc(some_date)` returns midnight on the day of `some_date`. If you pass in a `DATE` that represents August 22, 2012 at 12:42:00 am, you would get back August 22, 2012 at midnight. Adding an interval moves the date forward in time in this case by the specified number of hours (though you can specify intervals using other measures (i.e. hours, minutes, seconds, days, etc.). – Justin Cave Aug 22 '12 at 04:45
  • +1. I like better using: `WHERE some_date_col BETWEEN trunc(sysdate-1) + 17/24 AND trunc(sysdate) + 7/24 -- ;)` – Nir Alfasi Aug 22 '12 at 05:18