Home » SQL & PL/SQL » SQL & PL/SQL » time difference between two date
time difference between two date Thu, 21 September 2006 07:49
 gauravpuri Messages: 26Registered: August 2005 Location: Chennai Junior Member
hi

How to find out time difference between two dates. Is there is any build in for this.

Regards
Gaurav
Re: time difference between two date [message #194309 is a reply to message #194303] Thu, 21 September 2006 08:02
 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member
In the words of Tom Kyte, It depends.

You can find out the time difference between two dates by subtracting one from the other.

Formatting this is a little trickier.

You can do:
`SELECT to_char(trunc(sysdate) + (date_1 - date_2),'hh24:mi:ss') from dual;`

or expand it manually by multiplying by 24 to get the Hours, then multiplying the decimal remainder by 60 to get the minutes, and multiplying the decimal remainder of that by 60 to get the seconds.
Re: time difference between two date [message #194482 is a reply to message #194303] Fri, 22 September 2006 06:20
 amul Messages: 252Registered: April 2001 Location: Chennai Senior Member
since i feel that he must be newbie..
you will be able to see the time difference only if it has been entered in the table along with the data.else it will show the difference between 12:00 am to 12:00 AM,since i faced this problem when i was new..thought i should post this.
Re: time difference between two date [message #198021 is a reply to message #194303] Fri, 13 October 2006 19:17
 Kevin Meade Messages: 2101Registered: December 1999 Location: Connecticut USA Senior Member
gauravpuri

What the two prior poster have said is this:

select date1 - date2 ...

gives the number of days between the two dates. A difference of 1 days thus = 1 and difference of half a day thus equal .5 etc. From here you can use the fact that there are 24 hours in a day, 60 minutes in an hour and 60 seconds in a minute to figure whatever level of day granularity you want. Just do some math.

Additionally, the second poster is pointing out that a date is only as good as what you put it. If you want a date to have a time component you must enter a date with a time component. It sounds obvious but many tools don't do it. Oracle Forms for example has in the past had this issue, of trucating the portion of day from the date. If you are not seeing any difference when subtracting two dates that you thought should give a difference, it may be their time components were truncated when the dates were entered into your system. This was a problem for many tools because people assumed that all dates entered via a tool would contain a time portion when in fact the tool they were using (whatever it was) didn't.

Good luck, Kevin
 Previous Topic: Integrity Constraint error Next Topic: PRAGMA
Goto Forum:

Current Time: Sat May 27 08:55:09 CDT 2017

Total time taken to generate the page: 0.05174 seconds