Firebird: Retorna a data inicial e final por semana de um periodo

0

Posted on : 07-10-2011 | By : Paulo H Oliveira | In : Firebird

Este exemplo retorna a data inicial e final por semana de um periodo:

ex.:
periodo entre 11/09/2011 até 08/10/2011 retorna as seguintes datas:

RS1 RS2
2011-09-11 2011-09-17
2011-09-18 2011-09-24
2011-09-25 2011-10-01
2011-10-02 2011-10-08
EXECUTE BLOCK
RETURNS (RS1 VARCHAR(30),
         RS2 VARCHAR(30))
AS
DECLARE VARIABLE V_DTINI TIMESTAMP;
DECLARE VARIABLE V_DTFIM TIMESTAMP;
DECLARE VARIABLE V_SEMANA1 TIMESTAMP;
DECLARE VARIABLE V_SEMANA2 TIMESTAMP;
DECLARE VARIABLE VSQL VARCHAR(1024);
BEGIN
  V_DTINI = '11.09.2011';
  V_DTFIM = '08.10.2011';
 
  WHILE (CAST(V_DTINI AS DATE) <= CAST(V_DTFIM AS DATE))
  DO
  BEGIN
      VSQL = 'SELECT (CAST('||''''||SUBSTRING(V_DTINI FROM 1 FOR 10)||''''||' AS DATE) - EXTRACT(WEEKDAY FROM CAST('||''''||SUBSTRING(V_DTINI FROM 1 FOR 10)||''''||' AS DATE)) ),'||
             '    (CAST('||''''||SUBSTRING(V_DTINI FROM 1 FOR 10)||''''||' AS DATE) + 6 - EXTRACT(WEEKDAY FROM CAST('||''''||SUBSTRING(V_DTINI FROM 1 FOR 10)||''''||' AS DATE)) )'||
             'FROM RDB$DATABASE';
      FOR EXECUTE STATEMENT VSQL
                            INTO :V_SEMANA1,
                                 :V_SEMANA2
      DO
      BEGIN
        RS1 = SUBSTRING(:V_SEMANA1 FROM 1 FOR 10);
        RS2 = SUBSTRING(:V_SEMANA2 FROM 1 FOR 10);
        SUSPEND;
        V_DTINI = CAST(CAST(V_DTINI AS DATE) + 7 AS VARCHAR(30));
      END
  END
END

Desenvolvedor: Carlos Alberto Gaspar

Compartilhe :

  • Stumble upon
  • twitter

Escreva um comentário

Security Code: