CODEWITHSUNDEEP
oracle-databasedate-arithmetic
Naoual SMAILI
Naoual SMAILI

Reputation: 13

Next week in Oracle

I'm using oracle dbms and I have in Employe table a column Birthdate. I want to write a query that shows the employees who has a birthday next week. Is this correct ?

select name 
from employe 
where  to_char(birthdate,'DD-MM')=to_char(next_day(sysdate,1)+7,'DD-MM');

Upvotes: 1

Views: 1579

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248215

The correct solution would be

SELECT name
FROM employe
WHERE to_char(birthdate
              /* "move" the birthdate to the current year
                 to get a reliable week number */
              + CAST((EXTRACT(year FROM current_date)
                      - EXTRACT(year FROM birthdate)) || '-0'
                     AS INTERVAL YEAR TO MONTH),
              'IW')
    = to_char(current_date + 7, 'IW');

The IW format returns the ISO week containing the date, which is probably what you are looking for. If you start your week on Sunday, add one to both dates.

Upvotes: 1

APC
APC

Reputation: 146349

That is not the correct usage of next_day(): that function returns the date of the the next instance of a day. For example, to find the date of next Friday:

select next_day(sysdate, 'FRIDAY') from dual;

To find employees whose birthday is seven days from now, you need to just tweak your query a bit: 

select name 
from employe
where to_char(birthdate,'DD-MM') = to_char(sysdate+7,'DD-MM');

Upvotes: 2

Related Questions