Search code examples
oracledate-arithmetic

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');
Solution

  • 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.