CODEWITHSUNDEEP
sqloracle-databasedateoracle11g
thursdaysgeek
thursdaysgeek

Reputation: 7926

In Oracle, find the next specific date

I'm writing a procedure and I need to compare dates against a specific date in the future. I want to default to the last day of February. So if I'm running the report in January or February, the date will be Feb 28 (or 29 if a leap year) of the same year. If I'm running the report in March or later, the date will be Feb 28 (or 29) of the following year.

Is there an easier way to do that besides parsing the month and year, then creating a date by setting the month and day to March 1 minus 1 day, and the year to year+1 if the month is >= 3?

Upvotes: 1

Views: 1553

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

You're essentially using March 1st as the start of the year, so you could use the add_months() function to adjust the date forward 10 months, find the start of that (actual calendar) year, and then find the first day of March in that adjusted year, and find the day before that:

add_months(trunc(add_months(sysdate, 10), 'YYYY'), 2) - 1

SQL Fiddle demo with a CTE to generate a large range of dates so you can see those and which date is calculated for each.

Upvotes: 5

Justin Cave
Justin Cave

Reputation: 231651

You can use last_day to get the last day of the month and add_months to add a number of months to a date. There is probably a cute mathemetical way to avoid the case statement as well that I'm not thinking of

-- Showing the results that you would get if you ran on various dates.
-- In reality, you'd get rid of the CTE and just use sysdate in the query
with x as (
    select date '2015-01-01' dt from dual union all
    select date '2015-04-01' from dual union all
    select date '2015-02-28' from dual
 )
 select dt, 
        last_day( 
           add_months(dt, 
                      (case when extract(month from dt) <= 2 
                            then 2-extract(month from dt)
                            else 14-extract(month from dt)
                         end)
                      )
                 )
   from x

Upvotes: 1

Nick
Nick

Reputation: 2514

If the month is Feb or Jan, then it gets the current year and set's the day to March 1 minus 1. Else, gets current year adds one to the year, then sets the day to March 1 minus 1. 

select case when extract(month from current_date) in (1,2) then to_date(extract(year from current_date) ||  '03','YYYYMM')-1 else to_date(extract(year from current_date)+1 ||  '03','YYYYMM')-1 end
from dual;

Upvotes: 0

Related Questions