Hi Experts,
I have a scenario where i need to calculate No. of years and No. of months between 2 dates.
I have calculated no of days between 2 dates using this conversion.
interval_to_char(sysdate()-PURCHASE_DT,'D')
For eg. Suppose no of days is 765 then in YEAR column 2 should be mapped and in MONTH column 1 should be mapped.
No. of Days Year Month
765 2 1
For Year i have used this:
floor(cast(interval_to_char(sysdate()-PURCHASE_DT,'D'),'INT')/365)
Please help me with Month Column.
Thanks
Neha Khetan
Hi Neha,
try this,
for no of years ---- date_diff('2011.09.12','2013.06.26','YY')
for no of months -----date_diff('2011.09.12','2013.06.26','MM')%12
Hi Neha,
I would recommend you to use datatype as decimal(10,2) instead of INT to calculate the exact Years and months for given no. of days.
For YEAR you can use below code:
(cast(interval_to_char(sysdate()-PURCHASE_DT,'D'),'DECIMAL(10,2)')/365)
For MONTH you can use below code:
(cast(interval_to_char(sysdate()-PURCHASE_DT,'D'),'DECIMAL(10,2)')/365)*12
If you take a date 29-04-2009 as an example then above code shall give the following result.
DAYS: 1780
YEAR: 4.87
MONTH: 58.52
Hope this is what you are looking for.
Regards !
Add a comment