Return Anniversary of Hiring Weekly in SQL Oracle

Question

I need to provide information on the hiring anniversaries per week. Exemplifying once a week HR will send a message of congratulations to the hiring birthday.

I created the following SQL query:

SELECT

    HIRE_DATE AS DATA_CONTRATACAO,
    EXTRACT(DAY FROM HIRE_DATE) AS DIA_CONTRATACAO,
    EXTRACT(MONTH FROM HIRE_DATE) AS MES_CONTRATACAO,
    SYSDATE AS HOJE,
    SYSDATE+7 AS DATA_LIMITE


FROM
    EMPLOYEES

WHERE EXTRACT(MONTH FROM HIRE_DATE) = EXTRACT(MONTH FROM SYSDATE) 
        AND EXTRACT(DAY FROM HIRE_DATE) >= EXTRACT(DAY FROM SYSDATE+7)

There is a logic error because only the separate days we lose the month reference as in the example bettwen.

AND EXTRACT(DAY FROM HIRE_DATE) >= EXTRACT(DAY FROM SYSDATE+7)

Answer 1

Just change the logic of yours where.

Convert the date to the current year thus:

TO_DATE(TO_CHAR(hire_date,'DD/MM'),'DD/MM')

and then compare the dates using a between:

TO_DATE(TO_CHAR(hire_date,'DD/MM'),'DD/MM') BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE + 7)

See spinning here.