Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert fixed months, weeks or years to days in oracle

Posted on 2016-09-16
9
Medium Priority
?
227 Views
Last Modified: 2016-09-19
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


shelf life
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?
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41801965
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?
0
 
LVL 32

Expert Comment

by:awking00
ID: 41801995
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41802012
Rather than multiplying, do you have a start date?  If so, we can use the actual calendar rather than make multiplicative approximations.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 35

Expert Comment

by:johnsone
ID: 41802025
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)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41802029
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41802145
Okay. With expire_date and sysdate you have the dates you need to calculate days and johnsone gave you precisely what those calculations are.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 41802592
This question differs slightly (I think) from the question made yesterday.

a. Yesterday the table involved building things
b. Here the table is manufacturer's specifications

for a. there has to be a date of receipt (or similar) otherwise you cannot build/repair
for b. the purpose may be comparisons and not involve any concrete date of receipt

Given that the topic under discussion is aircraft parts then (in my view) you need to be "conservative" (as lives are at risk) so I suggest:

a "year" is 365 days
a "month" is treated as 30 days (round down 365/12)
and a week obviously 7 days

Because the topic is aircraft parts there will probably be standards that apply but I am no longer part of the aerospace industry so I'm hopelessly out of date on those.

so, for approximations in the common unit of days:
SELECT
      part
    , mfg_shelf_life
    , mfg_shelf_life_timeframe
    , mfg_shelf_life 
      * ( case when mfg_shelf_life_timeframe = 'Y' then 365
               when mfg_shelf_life_timeframe = 'M' then 30
               when mfg_shelf_life_timeframe = 'W' then 7
          end ) 
                    AS mfg_shelf_life_in_days_approx
FROM dss.part_specs pspec
WHERE mfg_shelf_life_timeframe IN ('Y', 'W', 'M')

Open in new window

0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 1000 total points
ID: 41803351
Regardless of the field names, the math is the same.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41804564
Thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question