To find a specific date after some given date in SQL oracle.

To find a specific date after some given date in SQL oracle.

Example: Date of joining of employee= 17-Dec-1980.

And I wish to get D开发者_开发百科ate and Day and time at start of very first 1st-Feb after given date of joining.

One option would be

add_months( trunc( add_months(<<your date>>,-1),'YYYY'), 13 )

i.e.

SQL> ed
Wrote file afiedt.buf

  1  select add_months( trunc( add_months( date '1980-02-17', -1 ), 'YYYY'), 13
)
  2*   from dual
SQL> /

ADD_MONTH
---------
01-FEB-81

SQL> ed
Wrote file afiedt.buf

  1  select add_months( trunc( add_months( date '1980-01-17', -1 ), 'YYYY'), 13
)
  2*   from dual
SQL> /

ADD_MONTH
---------
01-FEB-80

SQL> ed
Wrote file afiedt.buf

  1  select add_months( trunc( add_months( date '1980-12-17', -1 ), 'YYYY'), 13
)
  2*   from dual
SQL> /

ADD_MONTH
---------
01-FEB-81

You could try:

select add_months(trunc( date '1980-12-17' - 32, 'YEAR') + 31, 12)
from dual

In this query, 32 is the number of the day within the year of February 1. It can be adapted for other dates in January and February, but not for target dates later in the year due to the leap year irregularities.

I do it a little differently, but this seems a little clearer to me than the add_months method:

SELECT
    employee_join_date
  , CASE
        WHEN extract(MONTH FROM employee_join_date) >= 2
            THEN to_date(extract (YEAR FROM employee_join_date)+1 || '0201', 'yyyymmdd')
        ELSE to_date(extract (YEAR FROM employee_join_date) || '0201', 'yyyymmdd')
    END AS following_feb_1
FROM
    YOUR_TABLE;

Note that if the employee was hired on February 1, this will return February 1 of the following year. If you don't want that just use "> 2" instead of ">= 2".