Converting weeks, months and years

Recently there was a need to convert start and end dates to differing units of weeks, months and years in PL/SQL.
Although Oracle provides many date manipulation tools there are limited built in functions to do this sort of conversion. So here is a look at some approaches to do this sort of work.

For many of these examples the requirement was to return value as whole numbers and round up to the next highest value. I.e. If something was 40 days that means it cross 2 months. This is done using CEIL. If you want to round to the nearest value use ROUND or to use the lower value use TRUNC.

Using a start and end date

For weeks you use:

SELECT CEIL((end_date - start_dt)/7) FROM dual

So if end_date - start_dt = 23, then 23 / 7 = 3.285.., CEIL(3.285..) = 4
23 days gives us 4 weeks which is what we want.

For months you use:


SELECT CEIL(months_between(end_date, start_dt)) FROM DUAL

So if end_date is 31/12/2011 and start_dt is 17/02/2011 then months_between gives 10.451.., CEIL(10.451..) = 11 months.
Looking at the start and end date in this example we can see that it covers 11 whole months so this makes sense.

For years it is the same sort of thing but we just need to divide by 12


SELECT CEIL(months_between(end_date, start_dt)/12) FROM DUAL

End date is 31/12/2014, start_dt is 17/02/2011, month_between = 46.451..., divide by 12 = 3.870.., CEIL(3,870..) = 4
Again based on the start and end date 4 years is what we expect.

When converting units without specific dates

But what if I want to convert straight from 32 weeks to a number of months?

We know that a week is 7 days so we can just use an end date of sysdate and start date of sysdate - (32 * 7).
SELECT CEIL(months_between(sysdate , sysdate - (32 * 7))) FROM DUAL


Convert months to years or years to months is simply CEIL(number_of_months/12) and CEIL(number_of_years * 12)

Convert months to days.
This is never going to be 100% accurate if it is a generic calculation. We know some months have 30 days, some 31 and of course February can have 28 or 29. So it really depends what specific months you want to convert. If we don't have specific months then you can get close to an accurate figure using.

SELECT SYSDATE - ADD_MONTHS(SYSDATE, -number_of_months) FROM dual


So for 7 months
SELECT SYSDATE - ADD_MONTHS(SYSDATE, -7)FROM dual
gives a result of 212 days

Convert years to days.
This also present problems because a leap year has an extra day. So again it depends which set of years you want to convert. For financial processing the number of days in a year is usually set as 365.25 so the extra day is distributed across the four years.

Another way to do it without using the 365.25 is use the add_month built_in as well.

SELECT SYSDATE - ADD_MONTHS(SYSDATE, (-12 * number_of_years)) FROM dual


which for 4 years = 1461 and 3 years = 1095 when run right now. If you want to make sure it always gets the same answer then work from a standard date instead of sysdate. E.g. set the date to to_date('01/01/2000') instead of sysdate.

There may be a more accurate way of doing these calculations but I have not come across it yet.