OraFAQ Forum: SQL & PL/SQL » how do i get the total differnce of 2 dates through sql statement

Home » SQL & PL/SQL » SQL & PL/SQL » how do i get the total differnce of 2 dates through sql statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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..

Report message to a moderator

Re: how do i get the total differnce of 2 dates through sql statement [message #19125 is a reply to message #19124]

Fri, 01 March 2002 02:59

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

select (date1-date2)*24*60 from dual;

select (date1-date2)*24*60*60 from dual;

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	One error
Next Topic:	breaking column values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:16:10 CDT 2024