Geek Answers Handbook

To find a specific date after a specific date in Oracle oracle.

To find a specific date after a specific date in Oracle oracle.

Example: Employee affiliation date = December 17th, 1980.

And I want to get the date and day and time at the beginning of the very first February 1st after the specified date of accession.

+4
source share
3 answers

One of the options:

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
+3
source

You can try:

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

In this query, 32 is the day number of the year on February 1. It can be adapted for other dates in January and February, but not for target dates at the end of the year due to errors in a leap year.

0
source

I do it a little differently, but it 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;

Please note that if an employee was hired on February 1, he will return on February 1 of the following year. If you do not want, just use "> 2" instead of "> = 2".

0
source

More articles:


All Articles