Home » SQL & PL/SQL » SQL & PL/SQL » date difference
date difference [message #209453] Thu, 14 December 2006 23:46 Go to next message
Messages: 15
Registered: December 2006
Junior Member
How to achieve result in DD:HH:MM:SS format after subtracting two date columns (USING SELECT QUERY) like

open_date close_date

26-11-2006 11:20:00 27-11-2006 12:20:00

answer should come in format : DD:MM:HH:SS

Re: date difference [message #209461 is a reply to message #209453] Fri, 15 December 2006 00:25 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
to_char(trunc(closed-open),'09') || ':' || to_char(TRUNC(SYSDATE) + (closed - open), 'hh24:mi:ss')

Ross Leishman
Re: date difference [message #209464 is a reply to message #209461] Fri, 15 December 2006 00:33 Go to previous message
vamsi kasina
Messages: 2108
Registered: October 2003
Location: Hyderabad, India
Senior Member
Good one. I think OP has asked only for DD.
If he wants DDD he needs to changes '09' to '009'.

I tried another one.
Need to change a bit
Previous Topic: Removing Empty space from tables without exporting/importing
Next Topic: how to calculate total duration of the course hours
Goto Forum:

Current Time: Fri Aug 18 11:30:21 CDT 2017

Total time taken to generate the page: 0.07115 seconds