Making Oracle find the span of a year

Here’s a handy Oracle date tip. If you have a string for a year ‘2010’ or whatever as &tyear and feed it to this SQL. It will give back the first second and the last second of the year.

-- first/last day of year
select TRUNC(chart_date,'YEAR') first_day,
       last_day(add_months(chart_date,12 - to_number(to_char(chart_date,'mm')))) + interval '23:59:59' hour to second last_day 
from (select to_date(&tyear,'YYYY') chart_date from dual); 

Related Posts:

This entry was posted in Programming and tagged , , . Bookmark the permalink.

Comments are closed.