 Date Calculation and Conversion to Year, Month, Day format [message #206296] |
Wed, 29 November 2006 09:18  |
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  |
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 #206310 is a reply to message #206296] |
Wed, 29 November 2006 09:51 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
