asked on

Convert fixed months, weeks or years to days in oracle

SELECT nvl(mfg_shelf_life, -1)
            ,nvl(mfg_shelf_life_timeframe,'x')
      FROM dss.part_specs pspec
      where mfg_shelf_life_timeframe in ('Y','W','M')

Open in new window

M - Months
Y Years
W Weeks.

Right now I do not have data with weeks.

I need to take that value and convert to days. Can I get a query to get these values?

Sean Stuber

The result is simple, it's just multiplication.

The tricky part is what to multiply by..

How long is a month? 28, 29, 30, 31 days? something else, if so what?
How long is a year? 365 or 366 days? something else, if so what?

awking00

Weeks are easy, they all have 7 days. But years and months are not fixed, you'll need to have dates in order to calculate days.
The days between 01-JAN-2016 and 01-JAN-2017 are 366 but the days between 01-JAN-2017 and 01-JAN-2018 are 365.
The days between 01-JAN-2016 and 01-MAR-2016 are 60 but the days between 01-JUL-2016 and 01-SEP-2016 are 62.

Sean Stuber

Rather than multiplying, do you have a start date? If so, we can use the actual calendar rather than make multiplicative approximations.

johnsone

I posted those calculations in the question you asked yesterday. You should be able to pull them out of the function, but if you can't, here they are:

weeks to days:

shelf_life_weeks * 7

months to days:

expire_date - Add_months(expire_date, shelf_life_months * -1)

years to days:

expire_date - Add_months(expire_date, shelf_life_years * -12)

johnsone

Not sure why you need the calculations to convert to days, as the function that was posted yesterday took care of that calculation internally. All you have to do is pass in the number of weeks/months/years and it figures it out.

awking00

Okay. With expire_date and sysdate you have the dates you need to calculate days and johnsone gave you precisely what those calculations are.

ASKER CERTIFIED SOLUTION