| Urgent Date Problem [message #20631] |
Mon, 10 June 2002 00:36  |
Manoj
Messages: 101
Registered: August 2000
|
Senior Member |
|
|
HI Experts,
I have a problem that is related to Date .In my Table I have a to field of char type in which i store date as 'DD-MON-YYYY HH24:MI:SS' format. I want to calculate the deference between both column in terms of HH24:MI:SS
FOR THIS PURPOSE I WRITE THIS QUERY BUT I CANNOT GET MY SOLUTION .Please help me about this
SELECT IN_TIME ,OUT_TIME ,ABS(TO_DATE(IN_TIME,'DD-MON-YYYY HH24:MI:SS')-
TO_DATE(OUT_TIME,'DD-MON-YYYY HH24:MI:SS')) DEF
FROM TRY
;
RETURN :
IN_TIME : 10-JUN-2002 10:34:00
OUT_TIME : 10-JUN-2002 10:40:00
DIFF : .00416666666667
|
|
|
|
| Re: Urgent Date Problem [message #20634 is a reply to message #20631] |
Mon, 10 June 2002 05:04  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Looks right to me. The result is in days. Were you looking for it in minutes? In that case you have to convert by multiplying the result * 60 * 24:
1 select 60*24*
2 (to_date('10-JUN-2002 10:40:00', 'DD-MON-YYYY HH24:MI:SS')
3 - to_date('10-JUN-2002 10:34:00', 'DD-MON-YYYY HH24:MI:SS')) Diff_in_minutes
4* from dual
08:52:52 ==> /
DIFF_IN_MINUTES
---------------
6
|
|
|
|
| Re: Urgent Date Problem [message #20636 is a reply to message #20631] |
Mon, 10 June 2002 06:09 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
To get it out in the format you want, I'd create a function (this might need a little work, but you'll get the idea):
FUNCTION diffToString
(i_timeDiff IN NUMBER)
RETURN VARCHAR2
IS
l_timeDiff NUMBER := i_timeDiff;
l_hours NUMBER;
l_minutes NUMBER;
l_seconds NUMBER;
l_diff NUMBER;
BEGIN
l_hours := TRUNC(l_timeDiff*24);
l_timeDiff := MOD(l_timeDiff*24,l_hours);
l_minutes := TRUNC(l_timeDiff*60);
l_timeDiff := MOD(l_timeDiff*60,l_minutes);
l_seconds := TRUNC(l_timeDiff*60);
IF MOD(l_timeDiff, l_seconds) > 0 THEN
l_seconds := l_seconds + 1;
END IF;
RETURN(to_char(l_hours,'00')||':'||to_char(l_minutes,'00')||':'||to_char(l_seconds,'00'));
END;
and invoke that in the SQL, such as
1 select diffToString
2 (
3 (to_date('10-JUN-2002 10:40:00', 'DD-MON-YYYY HH24:MI:SS')
4 - to_date('10-JUN-2002 10:34:00', 'DD-MON-YYYY HH24:MI:SS'))
5 ) Diff
6* from dual
09:56:17 ==> /
DIFF
--------------------------------------------------------------------
00: 06: 00
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
