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.
Subscribe to:
Post Comments (Atom)
3 comments:
Why not use this query:
Select numtodsinterval([first_date] - [second_date], 'day') from dual;
This will return an interval of type day to second that can be compared. For your particular requirement if you could then check to see if the calculated interval was within your range:
Select case when numtointerval([first_date] - [second_date], 'day') between numtodsinterval(1,'second') and numtodsinterval(-1,'second') then 'Same' else 'Different' end compare_dates from dual;
In this second query the first calculation is compared to two additional intervals of plus and minus 1 second. If it falls in that range it's considered the same otherwise it's different.
Thanks for sharing. I am not aware of this function. Next time, I will try this and see if it works.
Thanks and regards,
Phani
Post a Comment