,

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.
/by
,

Close Notifications

Lot of time we want to close the Notification which has come to the user.

They are many ways to do it. The following script can be use to Close the Notification.

/*********************************************
— Date ‘Current Date’
— Author JPREDDY
— Purpose : Close Notifications
**********************************************/
BEGIN 
wf_notification.RESPOND( 12504888, — Notification ID.
‘Close requested by on ‘||Sysdate, — Comments
‘IM12345’ ); — Incident Number. 
COMMIT;
END; 
/You can know the status of the Notification from the following Query.

select * from wf_notifications
where NOTIFICATION_ID=12504888

There is some equal-vent API which does this work.

wf_notification.close(12280094 — Notification ID
,’SYSADMIN’);
/by