Home » SQL & PL/SQL » SQL & PL/SQL » need solution
need solution [message #385921] Wed, 11 February 2009 22:52 Go to next message
domnicdevassy
Messages: 4
Registered: February 2009
Location: mumbai
Junior Member
hi,

i want to calculate the difference between two date and time
for eg

time1 time2
6/24/2008 5:15:00 AM 6/24/2008 12:25:00 PM

so i want the result only in time format
as in the result of the above query should be

07:10:00

please help me out in this
thx a lot
Re: need solution [message #385924 is a reply to message #385921] Wed, 11 February 2009 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

>so i want the result only in time format
use TO_CHAR() after doing subtraction

You can see many, many, many examples on this forum if you will SEARCH for htem
Re: need solution [message #385936 is a reply to message #385921] Wed, 11 February 2009 23:30 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

@domnicdevassy
Read the stuff related to the extract function in oracle.

Following query would help you out.

SELECT
lpad(EXTRACT(HOUR FROM(to_date('6/24/2008 12:25:00 PM','MM/DD/YYYY HH:MI:SS AM') - to_date('6/24/2008 5:15:00 AM','MM/DD/YYYY HH:MI:SS AM')) DAY TO SECOND),2,'0') || ':' ||
lpad(EXTRACT(MINUTE FROM(to_date('6/24/2008 12:25:00 PM','MM/DD/YYYY HH:MI:SS AM') - to_date('6/24/2008 5:15:00 AM','MM/DD/YYYY HH:MI:SS AM')) DAY TO SECOND),2,'0') || ':' ||
lpad(EXTRACT(SECOND FROM(to_date('6/24/2008 12:25:00 PM','MM/DD/YYYY HH:MI:SS AM') - to_date('6/24/2008 5:15:00 AM','MM/DD/YYYY HH:MI:SS AM')) DAY TO SECOND),2,'0')  AS Interval
FROM dual


Regards
Srinivas
Re: need solution [message #385950 is a reply to message #385936] Thu, 12 February 2009 00:08 Go to previous messageGo to next message
domnicdevassy
Messages: 4
Registered: February 2009
Location: mumbai
Junior Member
thx a lot but when i use this query im getting the output as 00:00:00 can u help me out

SELECT
lpad(EXTRACT(HOUR FROM(to_date(COMPLETION_DATE,'DD/MM/YYYY HH:MI:SS ') - to_date(START_DATE,'DD/MM/YYYY HH:MI:SS ')) DAY TO SECOND),2,'0') || ':' ||
lpad(EXTRACT(MINUTE FROM(to_date(COMPLETION_DATE,'DD/MM/YYYY HH:MI:SS ') - to_date(START_DATE,'DD/MM/YYYY HH:MI:SS ')) DAY TO SECOND),2,'0') || ':' ||
lpad(EXTRACT(SECOND FROM(to_date(COMPLETION_DATE,'DD/MM/YYYY HH:MI:SS ') - to_date(START_DATE,'DD/MM/YYYY HH:MI:SS ')) DAY TO SECOND),2,'0') AS Interval
FROM XYZ
Re: need solution [message #385966 is a reply to message #385921] Thu, 12 February 2009 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select numtodsinterval(to_date('6/24/2008 12:25:00 PM','MM/DD/YYYY HH:MI:SS AM')
  2                         - to_date('6/24/2008 5:15:00 AM','MM/DD/YYYY HH:MI:SS AM'),
  3                         'DAY') diff
  4  from dual
  5  /
DIFF
---------------------------------------------------------------------------
+000000000 07:10:00.000000000

1 row selected.

Regards
Michel
Re: need solution [message #385989 is a reply to message #385966] Thu, 12 February 2009 03:01 Go to previous messageGo to next message
domnicdevassy
Messages: 4
Registered: February 2009
Location: mumbai
Junior Member
thx a lot..

but is there any other way to do it without using any kinds of function....
just the normal method.....
easy way out..
Re: need solution [message #386001 is a reply to message #385989] Thu, 12 February 2009 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "normal method".
Difference between to date is number of days. The rest is matter of artihmetics and you can do it yourself knowing there are 86400 seconds in a day, 1440 minutes and 24 hours.

Regards
Michel
Re: need solution [message #386069 is a reply to message #385950] Thu, 12 February 2009 08:04 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
domnicdevassy wrote on Thu, 12 February 2009 01:08
thx a lot but when i use this query im getting the output as 00:00:00 can u help me out

to_date(COMPLETION_DATE,'DD/MM/YYYY HH:MI:SS '



That's an example of why you do not use a TO_DATE on a DATE column. You use TO_DATE on a string. You use TO_CHAR on a DATE.

[Updated on: Thu, 12 February 2009 08:04]

Report message to a moderator

Previous Topic: Query to show both tables & sequences
Next Topic: Gererate Undo Query Logs corresponding to DDL/DML logs
Goto Forum:
  


Current Time: Mon Dec 05 14:50:52 CST 2016

Total time taken to generate the page: 0.14754 seconds