SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

select case date comparision

440538Feb 26 2007 — edited Feb 26 2007

I want to compare difference in time between to dates
t1 contains the start time of process
t2 contains end time of process
the querey works fine when the t1 and t2 date are same that is process date is same time may be differnt
the problem occurs when the t1 and t2 date are differen i have written a query with select case but this does not work . Please see the query and help .
Any examples would be appericated.

SELECT h.blncomplete,H.CODE,NVL(SUBSTR(H.DESCRIPTION,25,10),'Null') Records,t1,t2,
CASE T1
WHEN TRUNC(T1)=TRUNC(T2) THEN
ROUND(TO_NUMBER(TO_CHAR(T2,'HH24') * 60) TO_NUMBER(TO_CHAR(T2,'MI')) TO_NUMBER(TO_CHAR(T2,'SS')/60) - (TO_NUMBER(TO_CHAR(T1,'HH24') * 60)
+ TO_NUMBER(TO_CHAR(T1,'MI'))+ TO_NUMBER(TO_CHAR(T1,'SS')/60) )) TIME_mINUTE
WHEN TRUNC(T1)<>TRUNC(T2) THEN
ROUND(TO_NUMBER(TO_CHAR(T2,'HH24') * 60) + 24 * 60 TO_NUMBER(TO_CHAR(T2,'MI')) TO_NUMBER(TO_CHAR(T2,'SS')/60) - (TO_NUMBER(TO_CHAR(T1,'HH24') * 60)
+ TO_NUMBER(TO_CHAR(T1,'MI'))+ TO_NUMBER(TO_CHAR(T1,'SS')/60) )) TIME_mINUTE
FROM csuploadstage_HST H
WHERE TRUNC(T1)<>TRUNC(T2)
ORDER BY t1

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 26 2007

Added on Feb 26 2007

3 comments

386 views