Dias da Semana dentro do Mês
21/02/2018
0
Olá a todos.
Estou quebrando a cabeça com o DateUtils, mas ainda não cheguei no resultado que preciso. Vamos ao problema:
- Preciso que passando uma determinada data ele me retorne qual é o primeiro e o ultimo dia da semana dentro do mês. Exemplo:
Exemplo 1 - Passando dia 02/02/2018 preciso que ele me retorne "1ª SEMANA - 01 A 02";
Exemplo 2 - Passando dia 13/02/2018 preciso que ele me retorne "3ª SEMANA - 19 A 23";
OBS: Quando a data cair no fds ele deve escrever a data da próxima semana.
Exemplo: Passando dia 10/02/2018(sábado) preciso que ele me retorne "3ª SEMANA - 19 A 23";
Se eu conseguir no firebird fica melhor ainda... como está hj:
, CASE(EXTRACT(DAY FROM VENCIMENTO) / 7) + 1
WHEN 1 THEN '1ª SEMANA - 01 A 07'
WHEN 2 THEN '2ª SEMANA - 08 A 14'
WHEN 3 THEN '3ª SEMANA - 15 A 21'
WHEN 4 THEN '4ª SEMANA - 21 A 28'
WHEN 5 THEN '5ª SEMANA - MAIOR QUE 28'
END SEMANA
Alguém pode dar uma mão?
Obrigado a tds!
Estou quebrando a cabeça com o DateUtils, mas ainda não cheguei no resultado que preciso. Vamos ao problema:
- Preciso que passando uma determinada data ele me retorne qual é o primeiro e o ultimo dia da semana dentro do mês. Exemplo:
Exemplo 1 - Passando dia 02/02/2018 preciso que ele me retorne "1ª SEMANA - 01 A 02";
Exemplo 2 - Passando dia 13/02/2018 preciso que ele me retorne "3ª SEMANA - 19 A 23";
OBS: Quando a data cair no fds ele deve escrever a data da próxima semana.
Exemplo: Passando dia 10/02/2018(sábado) preciso que ele me retorne "3ª SEMANA - 19 A 23";
Se eu conseguir no firebird fica melhor ainda... como está hj:
, CASE(EXTRACT(DAY FROM VENCIMENTO) / 7) + 1
WHEN 1 THEN '1ª SEMANA - 01 A 07'
WHEN 2 THEN '2ª SEMANA - 08 A 14'
WHEN 3 THEN '3ª SEMANA - 15 A 21'
WHEN 4 THEN '4ª SEMANA - 21 A 28'
WHEN 5 THEN '5ª SEMANA - MAIOR QUE 28'
END SEMANA
Alguém pode dar uma mão?
Obrigado a tds!
Marcelo Letteri
Curtir tópico
+ 0
Responder
Posts
22/02/2018
Marcelo Letteri
Pessoal, resolvido via firebird:<br />
<br />
, CAST(<br />
iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE )))<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) )<br />
, ''1º Semana de '' || ( EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ))) || '' a '' || ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) )<br />
, iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) + 1 )<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 )<br />
, ''2º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) + 1 ) || '' a '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 )<br />
, iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 +1 )<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 )<br />
, ''3º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 +1 ) || '' a '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 )<br />
, iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 )<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 + 6 )<br />
, ''4º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 ) || '' a '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 + 6 )<br />
, ''5º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 + 6 + 1 ) || '' em diante''<br />
<br />
)<br />
)<br />
)<br />
) AS VARCHAR(40)<br />
) SEMANA<br />
<br />
<br />
RESOLVIDO
<br />
, CAST(<br />
iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE )))<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) )<br />
, ''1º Semana de '' || ( EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ))) || '' a '' || ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) )<br />
, iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) + 1 )<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 )<br />
, ''2º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) + 1 ) || '' a '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 )<br />
, iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 +1 )<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 )<br />
, ''3º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 +1 ) || '' a '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 )<br />
, iif( EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) >= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 )<br />
and EXTRACT( DAY FROM cast(PAR.PARVENCIMENTO as date) ) <= ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 + 6 )<br />
, ''4º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 ) || '' a '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 + 6 )<br />
, ''5º Semana de '' || ( ( ( 6 - EXTRACT( WEEKDAY FROM( CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) ) + EXTRACT( DAY FROM CAST( EXTRACT( MONTH FROM cast(PAR.PARVENCIMENTO as date) ) || ''-01-'' || EXTRACT( YEAR FROM cast(PAR.PARVENCIMENTO as date) ) AS DATE ) ) ) +1 + 6 + 1 + 6 + 1 + 6 + 1 ) || '' em diante''<br />
<br />
)<br />
)<br />
)<br />
) AS VARCHAR(40)<br />
) SEMANA<br />
<br />
<br />
RESOLVIDO
Responder
Clique aqui para fazer login e interagir na Comunidade :)
