Recipe 14.5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)ProblemYou want to find the number of days in a year.
SolutionUse 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 DiscussionBegin 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 |