quarta-feira, 30 de junho de 2021

Oracle - Query retuning start and end date per week number and year

 Query:

SELECT * FROM (SELECT WEEK_YEAR, WEEK_NUM, MIN(TODAY) START_DATE, MIN(TODAY) + 6 END_DATE

FROM (SELECT EXTRACT(YEAR FROM TODAY) WEEK_YEAR, TODAY, 

TO_CHAR(TODAY,'IW') "WEEK_NUM"

from (select (TO_DATE('01/01/'||TO_CHAR(2021), 'DD/MM/YYYY') - 1) + level TODAY 

from DUAL 

connect by LEVEL <= 368))

GROUP BY WEEK_YEAR, WEEK_NUM

ORDER BY START_DATE)

WHERE WEEK_YEAR = 2021

AND NOT (EXTRACT(MONTH FROM START_DATE) = 1 AND WEEK_NUM > 10)

Result:





Nenhum comentário:

Postar um comentário

Oracle - Listar datas do mês

 select TRUNC(SYSDATE)  + level - 1 dt from   dual connect by level <= (   LAST_DAY(SYSDATE) - TRUNC(SYSDATE) + 1 )