CODEWITHSUNDEEP
sqloracle-database
intruder
intruder

Reputation: 417

Extract date from a column of type date in SQL/ORACLE

I've a column of type DATE. How can I compare it with hours field of SYSDATE to perform some operation?

Column name: DateColumn - of type DATE

Requirement:
A search condition from the table considering the hours from DateColumn and should check if it is less than 10 hours from hours in SYSDATE

for ex, if hours in DateColumn is 15 and the current hours from SYSDATE is 20, as the hours difference is less than 10 hours, it should satisfy the condition.

I need something like this: 

 SELECT EMP_ID FROM EMPLOYEE WHERE SYSDATE(something to extract hours) - 
 DATECOLUMN(something to consider hours) < 10;

EDIT : It should consider the difference between the days too.

Like if DateColumn has 20-09-2017 15:00:000 and SYSDATE is 21-09-2017 20:00:000, it should fail as the hours difference is 29

Upvotes: 2

Views: 65

Answers (3)

MT0
MT0

Reputation: 167822

If you need to consider that 20:00 is only 8 hours before 04:00 then you can use:

SELECT *
FROM   employees
WHERE  ABS( MOD( ( SYSDATE - DateColumn ) * 24, 24 ) ) < 10;

If you need to include dates as well then:

SELECT *
FROM   EMPLOYEES
WHERE  DateColumn BETWEEN SYSDATE - INTERVAL '10' HOUR
                      AND SYSDATE + INTERVAL '10' HOUR;

Upvotes: 2

Mureinik
Mureinik

Reputation: 311088

You could use extract:

SELECT emp_id 
FROM   employee
WHERE  EXTRACT(HOUR FROM SYSDATE) - EXTRACT(HOUR FROM datecolumn) < 10;

Upvotes: 0

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

SELECT EMP_ID FROM EMPLOYEE WHERE TO_NUMBER(TO_CHAR(sysdate,'HH24')) - TO_NUMBER(TO_CHAR(sysdate,'HH24')) < 10;

Upvotes: 2

Related Questions