Query to get the Number of Seconds or Minutes or Hours between 2 given days
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.
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.
Leave a Reply
Want to join the discussion?Feel free to contribute!