Minus Time Query

[najm]

Dear
I have a query

DESC REGISTER

NAME TYPE
--------- ----------
EMP_ID NUMBER(10)
EMPCODE VARCHAR(10)
TIME_IN DATE
DUTYTIME_IN VARCHAR2(10)
TIMEIN_DIFF NUMBER(5)

SELECT EMP_ID, EMPCODE, TIME_IN , DUTYTIME_IN, TIMEIN_DIFF
FROM REGISTER;

Result Shows

EMP_ID EMPCODE TIME_IN DUTYTIME TIMEIN_DIFF
------ -------- --------- -------- -----------
194 000194 27-NOV-07 08:30:55
27 000027 27-NOV-07 08:30:55
55 000055 27-NOV-07 08:30:55

I want the Diffrence in TIME_IN and DUTYTIME
when i use to_char to conver TIME_IN(Date) into TIME then use minus it show error and i am unable to run

Please can any one help me in this query.


Thanks

Cheers

[ahmadbilal]

Try it out


SELECT TO_CHAR(TimeIn,'DD.MM.YYYY:HH24:MI:SS') "Time In",
TO_DATE(stdtimein,'DD.MM.YYYY:HH24:MI:SS')
- TimeIn "Late no of Days"
FROM Office_employee_time;


SELECT TO_CHAR(Timein,'DD.MM.YYYY:HH24:MI:SS') "Time In",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-Timein))-60*(trunc((86400*(&&Today-timein))/60)) "Sec",
trunc((86400*(&Today-Timein))/60)-60*(trunc(((86400*(&&Today-Timein))/60)/60)) "Min",
trunc(((86400*(&Today-Timein))/60)/60)-24*(trunc((((86400*(&&Today-Timein))/60)/60)/24)) "Hrs",
trunc((((86400*(&Today-Timein))/60)/60)/24) "Days"
FROM Office_employee_time;