Home » SQL & PL/SQL » SQL & PL/SQL » date substraction
date substraction [message #373024] Fri, 23 March 2001 09:56 Go to next message
Ajay Gondhiya
Messages: 2
Registered: March 2001
Junior Member
I have to columns to type "Date" in a table and I want to substract one from the other to find out the time difference between the two dates. I need the time difference in certain format e.g. "years-months-days hours:minutes:seconds" or something even simpler "hours:minutes:seconds". Is it possible ? I would appreciate if you can give me a one-liner SQL statement as an example. Thank you.

Ajay
Re: date substraction [message #373027 is a reply to message #373024] Fri, 23 March 2001 11:37 Go to previous messageGo to next message
aish
Messages: 44
Registered: March 2001
Member
Hi try this

SELECT col1 TRUNC(MONTHS_BETWEEN(SYSDATE,col1)/12,0)
Year,
TRUNC(MONTHS_BETWEEN(SYSDATE,col1))-TRUNC(MONTHS_BETWEEN(SYSDATE,col1))/12,0)*12,0)
Mon
This should give you the years and months differences

Thanx,
Aish
Re: date substraction [message #373046 is a reply to message #373024] Fri, 23 March 2001 13:21 Go to previous messageGo to next message
Ajay Gondhiya
Messages: 2
Registered: March 2001
Junior Member
Thanks for your help. The solution is good if I have dates far apart by months/years but sometimes I have the same date with time difference of few minutes/seconds so is it possible to achieve the time difference between two dates in hours:minutes:seconds format ? Thank you.

Ajay
Re: date substraction [message #385934 is a reply to message #373024] Wed, 11 February 2009 23:26 Go to previous messageGo to next message
domnicdevassy
Messages: 4
Registered: February 2009
Location: mumbai
Junior Member
hi

i got 2 fields

for eg

tim1
6/24/2008 5:15:00 AM

and time2
6/24/2008 12:25:00 PM

(time2 - tim1)

i want to get the output which will display the difference in time format as how many hrs, mins and secs difference.

as in the output should be in this case
07:10:00

i want it in this format
can u help me out in this
thx a lot

[Updated on: Wed, 11 February 2009 23:28]

Report message to a moderator

Re: date substraction [message #385935 is a reply to message #373024] Wed, 11 February 2009 23:29 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/385921/136107/#msg_385921

Posting to a thread years & years old might not be productive.
Previous Topic: Problems Wrapping package with pipelined functions
Next Topic: group the data on month and sort it on date
Goto Forum:
  


Current Time: Fri Dec 09 19:50:50 CST 2016

Total time taken to generate the page: 0.15672 seconds