[firebase-br] Pegar inteiro da divisão

[Luciano]

Se quiser utilize este conjunto de procedures que conseguirá isto e muito 
mais......
===================================================
CREATE OR ALTER PROCEDURE FU_IDADE(
    WINI DATE,
    WFIM DATE)
RETURNS (
    ANOS SMALLINT,
    MESE SMALLINT,
    DIAS SMALLINT)
AS
declare variable diai smallint;
declare variable mesi smallint;
declare variable anoi smallint;
declare variable diaf smallint;
declare variable mesf smallint;
declare variable anof smallint;
begin
if (:wini = :wfim) then
 begin
  anos = 0; mese = 0; dias = 1;
 end
else if (:wini > :wfim) then
 begin
  anos = 0; mese = 0; dias = 0;
 end
else begin
 select rdia, rmes, rano from fu_decode_date(:wini) into :diai, :mesi, 
:anoi;
 select rdia, rmes, rano from fu_decode_date(:wfim) into :diaf, :mesf, 
:anof;
 anos = :anof - :anoi;

 if (:mesf >= :mesi) then
  mese = :mesf - :mesi;
 else begin
  if (:diaf >= :diai) then
   mese = (12 - :mesi) + :mesf;
  else
   mese = (12 - (:mesi + 1)) + :mesf;
 end

 if (:diaf >= :diai) then
  dias = :diaf - :diai + 1;
 else begin
  select r_data from fu_inc_dec_ano(:wini, :anos) into :wini;
  dias =0;

  while (:wini <= :wfim) do begin
   dias = :dias + 1;
   wini = :wini + 1;
  end
 end
end

suspend;
end

======================================

CREATE OR ALTER PROCEDURE FU_DECODE_DATE(
    WDAT DATE)
RETURNS (
    RDIA SMALLINT,
    RMES SMALLINT,
    RANO SMALLINT)
AS
begin
rdia = extract(day   from :wdat);
rmes = extract(month from :wdat);
rano = extract(year  from :wdat);

suspend;
end

====================================

CREATE OR ALTER PROCEDURE FU_INC_DEC_ANO(
    WDAT DATE,
    WQTD SMALLINT)
RETURNS (
    R_DATA DATE)
AS
declare wmes varchar(10);
declare wres smallint;
declare trab varchar(10);
begin
select data_tela from fu_datatela(:wdat, null, 'E') into :trab;
wmes = substring(:trab from 4 for 2);

if (:wmes <> '02') then
  r_data = cast(cast(extract(year from :wdat) + :wqtd as char(4)) || '-' ||
                substring(:trab from 4 for 2) || '-' ||
                substring(:trab from 1 for 2) as date);
else begin
 if (extract(day from :wdat) = 29) then
  begin
   select r_mod from fu_mod(:wqtd, 4) into :wres;

   if (:wres = 0) then
    r_data = cast(cast(extract(year from :wdat) + :wqtd as char(4)) || '-' 
||
                  substring(:trab from 4 for 2) || '-' ||
                  substring(:trab from 1 for 2) as date);
   else
    r_data = cast(cast(extract(year from :wdat) + :wqtd as char(4)) || '-' 
||
                  substring(:trab from 4 for 2) || '-28' as date);
  end
 else
  r_data = cast(cast(extract(year from :wdat) + :wqtd as char(4)) || '-' ||
                substring(:trab from 4 for 2) || '-' ||
                substring(:trab from 1 for 2) as date);
end

suspend;
end

========================================

CREATE OR ALTER PROCEDURE FU_MOD(
    WRAD INTEGER,
    WDIV INTEGER)
RETURNS (
    R_MOD INTEGER)
AS
begin

if(:wrad < :wdiv) then
 r_mod = :wdiv;
else
 R_MOD = :WRAD - ((:WRAD / :WDIV) * :WDIV);

suspend;
end

=============================

CREATE OR ALTER PROCEDURE FU_DATATELA(
    DAT1 DATE,
    DAT2 TIMESTAMP,
    TIPO CHAR(1))
RETURNS (
    DATA_TELA VARCHAR(19))
AS
declare variable trab varchar(25);
begin
tipo = upper(:tipo);

if(:dat1 is not null) then
 begin
  if (:tipo = 'E') then
   data_tela = substring(cast(:dat1 as varchar(10)) from 9 for 2) || '/' ||
               substring(cast(:dat1 as varchar(10)) from 6 for 2) || '/' ||
               substring(cast(:dat1 as varchar(10)) from 1 for 4);
  else
   data_tela = substring(cast(:dat1 as varchar(10)) from 9 for 2) || '/' ||
               substring(cast(:dat1 as varchar(10)) from 6 for 2) || '/' ||
               substring(cast(:dat1 as varchar(10)) from 3 for 2);
 end
else begin
 trab = cast(:dat2 as varchar(25));

 if (tipo = 'E') then
  data_tela = substring(:trab from 9 for 2) || '/' ||
              substring(:trab from 6 for 2) || '/' ||
              substring(:trab from 1 for 4) || ' ' ||
              substring(:trab from 12 for 8);
 else
  data_tela = substring(:trab from 9 for 2) || '/' ||
              substring(:trab from 6 for 2) || '/' ||
              substring(:trab from 3 for 2) || ' ' ||
              substring(:trab from 12 for 8);
end

suspend;
end
=======================================

Boa Sorte

Luciano C Santos



----- Original Message ----- 
From: "Wilson Mota - GMail" <wmotasjrp em gmail.com>
To: "Lista FireBase" <lista em firebase.com.br>
Sent: Thursday, November 06, 2008 4:17 PM
Subject: [firebase-br] Pegar inteiro da divisão


Ola pessoal.

Preciso saber a qtde de anos entre duas datas. Executando esse select tenho 
um numero fracionado,
eu preciso pegar a parte inteira que representa os anos. Existe algum 
comando nativo no FB 2.04

select (CURRENT_TIMESTAMP - DTADMISSAO) / 360
from FUNCIONARIO
WHERE ID_FUNCINARIO = 1

Obrigado.

Wilson
______________________________________________
FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
Para saber como gerenciar/excluir seu cadastro na lista, use: 
http://www.firebase.com.br/fb/artigo.php?id=1107
Para consultar mensagens antigas: http://firebase.com.br/pesquisa