Home » SQL & PL/SQL » SQL & PL/SQL » Date Calculation and Conversion to Year, Month, Day format
icon5.gif  Date Calculation and Conversion to Year, Month, Day format [message #206296] Wed, 29 November 2006 09:18 Go to next message
hrittenhouse
Messages: 3
Registered: November 2006
Junior Member
Hi, When I execute the following query:

select (SYSDATE - P_DATE)/365
FROM DW_TABLE

I get a number of years which is the difference between the two dates. I would like to format the number of years into a Year, Month, Day format but can't figure it out.

Can someone please help me?

Thank you.
Re: Date Calculation and Conversion to Year, Month, Day format [message #206305 is a reply to message #206296] Wed, 29 November 2006 09:43 Go to previous messageGo to next message
vgs2005
Messages: 123
Registered: April 2005
Senior Member
Can you give an example with the result that you like to get? Because this is a little confusing. If you get 2.25, how exactly do you want to format it in Yr, month or Day? The only thing I can think of is to have:

select (SYSDATE - P_DATE)/365 YEAR,
       ((SYSDATE - P_DATE)/365)* 12 MONTH,
       ((SYSDATE - P_DATE)/365)* 365 DAY
FROM DW_TABLE
Re: Date Calculation and Conversion to Year, Month, Day format [message #206307 is a reply to message #206296] Wed, 29 November 2006 09:46 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Some discussion here:

http://asktom.oracle.com/pls/ask/f?p=4950:8:8125986680151446499::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:96012348060
Re: Date Calculation and Conversion to Year, Month, Day format [message #206310 is a reply to message #206296] Wed, 29 November 2006 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, you don't get the number of years. You get the number of days/365, which is close to the number of years, but slightly different (Hint - think leap years).

Your best bet to get the number of years between two dates is probably:
select months_between(SYSDATE,P_DATE)/12 
FROM DW_TABLE

As to formatting it as years, months, days, what you ask isn't really sensible.
It would be quite easy to say
Quote:
If we added that many days on to '1st Jan 1900', how many years and months would it add to the date, and what day of the month would it be
but the years, months and days value would change depending on your start date.

That being said, Oracle have had a pretty heroic stab at the problem, with the Interval expression.
icon7.gif  Re: Date Calculation and Conversion to Year, Month, Day format [message #206327 is a reply to message #206305] Wed, 29 November 2006 10:35 Go to previous messageGo to next message
hrittenhouse
Messages: 3
Registered: November 2006
Junior Member
The result, for instance, would be 22.25 years. I want to format it to say 22 years, 3 months, 2 days to get the exact amount of time spend in a job.

Thanks for your help!
icon6.gif  Re: Date Calculation and Conversion to Year, Month, Day format [message #206332 is a reply to message #206307] Wed, 29 November 2006 10:49 Go to previous message
hrittenhouse
Messages: 3
Registered: November 2006
Junior Member
The code found on the link posted (asktom.oracle.com.....) gave me exactly what I needed. Thank you so much!

Previous Topic: Checking for maximum value reached
Next Topic: Triggers and Date's
Goto Forum:
  


Current Time: Wed Dec 07 03:00:57 CST 2016

Total time taken to generate the page: 0.09326 seconds