Recipe14.5.Finding the Number of Days in a Year (an Alternate Solution for Oracle)


Recipe 14.5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)

Problem

You want to find the number of days in a year.

This recipe presents an alternative solution to "Determining the Number of Days in a Year" from Chapter 9. This solution is specific to Oracle.


Solution

Use the TO_CHAR function to format the last date of the year into a three-digit day-of-the-year number:

  1 select 'Days in 2005: '|| 2        to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD') 3        as report 4   from dual 5 union all 6 select 'Days in 2004: '|| 7        to_char(add_months(trunc( 8        to_date('01-SEP-2004'),'y'),12)-1,'DDD') 9   from dual REPORT ----------------- Days in 2005: 365 Days in 2004: 366 

Discussion

Begin by using the TRUNC function to return the first day of the year for the given date, as follows:

  select trunc(to_date('01-SEP-2004'),'y')   from dual TRUNC(TO_DA ----------- 01-JAN-2004 

Next, use ADD_MONTHS to add one year (12 months) to the truncated date. Then subtract one day, bringing you to the end of the year in which your original date falls:

  select add_months(          trunc(to_date('01-SEP-2004'),'y'),          12) before_subtraction,        add_months(          trunc(to_date('01-SEP-2004'),'y'),          12)-1 after_subtraction   from dual BEFORE_SUBT AFTER_SUBTR ----------- ----------- 01-JAN-2005 31-DEC-2004 

Now that you have found the last day in the year you are working with, simply use TO_CHAR to return a three-digit number representing on which day (1st, 50th, etc.) of the year the last day is:

  select to_char(          add_months(            trunc(to_date('01-SEP-2004'),'y'),            12)-1,'DDD') num_days_in_2004   from dual NUM --- 366 




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net