CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Kᴀτᴢ
Kᴀτᴢ

Reputation: 2176

oracle query between two dates with hours

This is my current oracle table:

enter image description here

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!

Upvotes: 7

Views: 82761

Answers (4)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

SELECT * 
FROM table 
WHERE date BETWEEN start_date +  NUMTODSINTERVAL( hour, 'HOUR' ) AND end_date +  NUMTODSINTERVAL( hour, 'HOUR' )

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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

Upvotes: 11

Praveen
Praveen

Reputation: 9335

If you want to add min to date you can use interval.

select sysdate, sysdate + interval '1' hour from dual

So,

Select * 
from Datatable 
where 
DATE BETWEEN myStartDate + interval to_char(HOURS) hour 
and myEndDate + interval to_char(HOURS) hour

Upvotes: 4

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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.

Upvotes: 7

Related Questions