Note:- I have prepared this query since I could not find any inbuilt functions or procedures in Oracle to get this. I had the requirement to treat 2 dates are same if differences is not more then 2 seconds.

For the Seconds:-
——————-

SELECT (‘Your first Date’-‘Your another Date’) * DECODE( UPPER(‘SS’)
, ‘SS’, 24*60*60 
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER(‘SS’)
, ‘SS’, 24*60*60
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

For Minutes:-
—————

SELECT (‘Your first Date’-‘Your another Date’) * DECODE( UPPER(‘MI’)
, ‘SS’, 24*60*60 
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER(‘MI’)
, ‘SS’, 24*60*60
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

For the Hours:-
—————–

SELECT (‘Your first Date’-‘Your another Date’) * DECODE( UPPER(‘HH’)
, ‘SS’, 24*60*60 
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER(‘HH’)
, ‘SS’, 24*60*60
, ‘MI’, 24*60
, ‘HH’, 24, NULL )
FROM DUAL;

This is the simple decode function made for all the above 3 requirements. I hope this will help you.
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply