Showing posts with label oracle datetime difference query time difference sql. Show all posts
Showing posts with label oracle datetime difference query time difference sql. Show all posts

Thursday, March 1, 2012

DateTime Diff Query

Sample query for finding out the difference between two ORACLE time stamp fields


SELECT PROCESS_INSTANCE, EMPLID, BUSINESS_UNIT,
WF_FROM_DTTM, WF_TO_DTTM,
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600)/60)
|| ' MINS ' ||
ROUND((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600 -
(FLOOR((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' AS "RUN TIME"
FROM PS_SCB_JUP_REQ_ACT WHERE SCB_JUP_APR_TYPE = 'CFP'