Home » SQL & PL/SQL » SQL & PL/SQL » date difference
date difference [message #209453] Thu, 14 December 2006 23:46 Go to next message
naiksushma
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


Report message to a moderator

Re: date difference [message #209461 is a reply to message #209453] Fri, 15 December 2006 00:25 Go to previous messageGo to next message
rleishman
Messages: 3728
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

Report message to a moderator

Re: date difference [message #209464 is a reply to message #209461] Fri, 15 December 2006 00:33 Go to previous message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
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
By
Vamsi

Report message to a moderator

Previous Topic: Removing Empty space from tables without exporting/importing
Next Topic: how to calculate total duration of the course hours
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 04:40:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.