Time difference [message #442782] |
Wed, 10 February 2010 08:45  |
ramesh55.sse
Messages: 262 Registered: December 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
Please help me on the folloewing scenario.
I want to find the Time difference for the following data
DD-MON-YYYY HH MI 24 Format
01-Jan-2010 22:00
03-Jan-2010 04:00
the output should be 6 Hours,how can we do that please help me.
Thanks in advance.
|
|
|
|
Re: Time difference [message #442785 is a reply to message #442782] |
Wed, 10 February 2010 08:50   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'd do something like this:with src as (select to_date('03-Jan-2010 04:00','dd-mon-yyyy hh24_mi') - to_date('01-Jan-2010 22:00','dd-mon-yyyy hh24:mi') col_1
from dual)
select (col_1 - trunc(col_1))*24 hours from src;
|
|
|
Re: Time difference [message #442799 is a reply to message #442782] |
Wed, 10 February 2010 12:22   |
ramesh55.sse
Messages: 262 Registered: December 2008 Location: Hyderabad
|
Senior Member |
|
|
If i changed the date it's showing different result.
i need a answer it should work to find difference between any dates.In this scenario i want to find the difference between
22:00 and 04:00 no need consider dates.
Please help me
|
|
|
|
Re: Time difference [message #442803 is a reply to message #442782] |
Wed, 10 February 2010 12:58   |
ramesh55.sse
Messages: 262 Registered: December 2008 Location: Hyderabad
|
Senior Member |
|
|
It should come 6 hours in the out put.
And also one more question why the following query showing
-0.583 as result
SELECT to_date('01-Jan-2010 08:00', 'dd-mon-yyyy hh24_mi') -
to_date('01-Jan-2010 22:00', 'dd-mon-yyyy hh24:mi') col_1
FROM dual
please help me.
|
|
|
Re: Time difference [message #442804 is a reply to message #442803] |
Wed, 10 February 2010 13:02   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:It should come 6 hours in the out put.
Why?
Quote:And also one more question why the following query showing -0.583 as result
Please read the answers we give you:
Quote:You can directly make the difference bewteen two dates, the result is in unit of days.
There are 24 hours per day, 60 minutes per hour and 60 seconds per minute.
Quote:select (col_1 - trunc(col_1))*24 hours from src;
Regards
Michel
[Updated on: Wed, 10 February 2010 13:03] Report message to a moderator
|
|
|
Re: Time difference [message #442812 is a reply to message #442804] |
Wed, 10 February 2010 13:47   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote:22:00 and 04:00 no need consider dates.
As if the Y2K bug was not enough, not people are working on a bugs that might strike every day at midnight. Or a few years from now when somebody decides to run an overnight batch at 01:00 instead of 22:00, and nobody remembers this buggy code, or the person who wrote it.
|
|
|
Re: Time difference [message #442888 is a reply to message #442799] |
Thu, 11 February 2010 03:28  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:If i changed the date it's showing different result.
i need a answer it should work to find difference between any dates.In this scenario i want to find the difference between
22:00 and 04:00 no need consider dates.
When I run it, as long as the date with a time of 04:00 is on a later day than the time,it works.
If you felt like providing any information at all, then I could be more help.
Why don't you try describing the problem that you're trying to solve, rather than get us to fix the slightly ropey solution you've chosen.
|
|
|