By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH EMPLOYEES AS
(
SELECT to_date('21/05/07 08:23:44','dd/mm/yy hh24:mi:ss') hire_date FROM dual UNION ALL
SELECT to_date('18/05/03 08:23:44','dd/mm/yy hh24:mi:ss') hire_date FROM dual UNION ALL
SELECT to_date('11/05/04 08:23:44','dd/mm/yy hh24:mi:ss') hire_date FROM dual UNION ALL
SELECT to_date('24/05/07 08:23:44','dd/mm/yy hh24:mi:ss') hire_date FROM dual UNION ALL
SELECT to_date('23/05/06 08:23:44','dd/mm/yy hh24:mi:ss') hire_date FROM dual
)
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 TO_DATE(TO_CHAR(hire_date,'DD/MM'),'DD/MM') BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE + 7)
| DATA_CONTRATACAO | DIA_CONTRATACAO | MES_CONTRATACAO | HOJE | DATA_LIMITE |
|---|---|---|---|---|
| 24-MAY-07 | 24 | 5 | 24-MAY-23 | 31-MAY-23 |