how do i get the total differnce of 2 dates through sql statement [message #19124] |
Fri, 01 March 2002 02:36 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
Hi,
I have 2 columns having dates.
Now i want the total difference of time in minutes and total difference of dates in seconds through sql statement.
I want 2 separate sql statements for each.
If the datatype of the column holding the date is date,what should be statement for each
and if the datatype holding the date value is varchar then what should be the select statement.
any help is appreciated..
|
|
|
|
Re: how do i get the total differnce of 2 dates through sql statement [message #19138 is a reply to message #19124] |
Fri, 01 March 2002 12:27 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
CREATE OR REPLACE FUNCTION to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR (TRUNC (i_days)) || ' days ' ||
TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1), 'HH24:MI:SS');
END to_hms;
/
select to_date('26-Jul-2000 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('20-Jul-2000 11:05:05', 'dd-Mon-yyyy hh24:mi:ss') from dual;
6.09392361111111
select to_hms(to_date('26-Jul-2000 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('20-Jul-2000 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from dual;
6 02:15:15
If one of your columns is varchar2, then just do a to_date(varchar2datecol, 'your format goes here') to convert it to date. You should use to_date even if implicit datatype conversion works.
|
|
|