oracle query between two dates with hours

Question

This is my current oracle table:

• DATE = date
• HOUR = number
• RUN_DURATION = number

I need a query to get RUN_DURATION between two dates with hours like

Select * from Datatable where DATE BETWEEN to_date('myStartDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS? and to_date('myEndDate', 'dd/mm/yyyy hh24:mi:ss') + HOURS?

For example all data between 30.10.14 11:00:00 and 30.10.14 15:00:00

I stuck to get the hours to the dates. I tried to add the hours into myStartDate but this will be ignored for the start date because of BETWEEN. I know BETWEEN shouldn't be used for dates but I can't try other opportunities because I don't know how to get DATE and HOUR together... Thanks!

Answer 1

Well, you can add hour to your field date this way

select "DATE" + (hour / 24) from <yourTable>

this will give you ( from your first sample, may be different based on your format)

August, 14 2015 10:00:00 
August, 14 2015 08:00:00 

Based on that, you can do any between, select that you need.

In your case

where "DATE" + (hour / 24 )

which would make

Select * 
from Datatable 
where "DATE" + (hour / 24 )
 BETWEEN to_date('30/10/2014 11:00:00', 'dd/mm/yyyy hh24:mi:ss')  and            
         to_date('30/10/2014 15:00:00', 'dd/mm/yyyy hh24:mi:ss') 

see SqlFiddle

(By the way, don't use reserved keywords for column name, but I guess this is just a sample).

Answer 2

A DATE has both date and time elements. To add hours to date, you just need to do some mathematics.

For example,

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> SELECT SYSDATE, SYSDATE + 1/24 FROM dual;

SYSDATE             SYSDATE+1/24
------------------- -------------------
2015-08-14 15:02:59 2015-08-14 16:02:59

Adds 1 hour to sysdate. So, if you have a column of date data type, just add number of hours to be added divided by 24 because you add number of days to a date. So, you just need to first convert the number of hours into date as hours/24.