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).