Procedure - Retorna último dia do mês
SQL:
create or alter procedure ULTIMODIAMES (MES integer, ANO integer) returns (ULTIMO_DIA smallint) as
declare variable VPROXMES integer;
declare variable DATA date;
begin
if (:mes is null) then
mes=EXTRACT(month FROM CURRENT_DATE);
if (:ano is null) then
ano=EXTRACT(YEAR FROM CURRENT_DATE);
vproxmes = :mes+1;
if (:mes=12) then
begin
ano= :ano+1;
vproxmes = 1;
end
DATA = '01.'||:vproxmes||'.'||ANO;
ultimo_dia = EXTRACT(DAY FROM (CAST(DATA AS DATE)-1));
suspend;
end
Chamada da procedure:
SELECT *
FROM ULTIMODIAMES(07, 2019)
Retorno:
31