[firebird-support] Age as a string and it's parts as integer

[venussof...@gmail.com [firebird-support]]

Dear all

Happy New Year!

Maybe there is already something out there but my requirements was that I need 
Age as a string 4y 5m 8d.  Maybe for calculation purposes I would want the 
components separately as integers.  Most of the functions I found was giving me 
the difference in years, months, days so I got around to creating the below 
SPs, hope it is useful for someone having requirements similar to mine.  There 
may be some bugs in the code, if there are please let me know what corrections 
you may have taken to iron them out.  Hopefully there is positive feedback 
about these SPs.

Kind regards
Bhavbhuti


 SET TERM ^ ;
CREATE PROCEDURE AGEGETSTRING (
    LDDAYOFBIRTH TIMESTAMP,
    LDUPTILDATE TIMESTAMP,
    LCRETURNTYPE CHAR(10) )
RETURNS (
    LCRESULT CHAR(250) )
AS
DECLARE VARIABLE ldThisYearBirthDay TIMESTAMP;
DECLARE VARIABLE ldLessFromBirthDay TIMESTAMP;
DECLARE VARIABLE liBuffer INTEGER;
DECLARE VARIABLE liYears INTEGER;
DECLARE VARIABLE liMonths INTEGER;
DECLARE VARIABLE liDays INTEGER;

BEGIN
    ldThisYearBirthDay = DATEADD(YEAR, DATEDIFF(YEAR, ldDayOfBirth, 
ldUptilDate), ldDayOfBirth);
    liBuffer           = IIF(ldThisYearBirthDay > ldUptilDate, 1, 0);
    
    liYears            = DATEDIFF(YEAR, ldDayOfBirth, ldUptilDate) - liBuffer;
    ldLessFromBirthDay = DATEADD(liYears YEAR TO ldDayOfBirth);

    liMonths           = DATEDIFF(MONTH, ldLessFromBirthDay, ldUptilDate) - 
liBuffer;
    ldLessFromBirthDay = DATEADD(liMonths MONTH TO ldLessFromBirthDay);

    liDays = DATEDIFF(DAY, ldLessFromBirthDay, ldUptilDate) - liBuffer;

    lcResult = IIF(UPPER(lcReturnType) = 'STR'
        , CAST(liYears AS INTEGER) || 'y ' || CAST(liMonths AS INTEGER) || 'm ' 
|| CAST(liDays AS INTEGER) || 'd'
        , IIF(UPPER(lcReturnType) = 'YEARS'
            , CAST(liYears AS INTEGER)
            , IIF(UPPER(lcReturnType) = 'MONTHS'
                , CAST(liMonths AS INTEGER)
                , IIF(UPPER(lcReturnType) = 'DAYS'
                    , CAST(liDays AS INTEGER)
                    , 'BAD_PARAM'
        ))));
    
    SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE AGEGETSTRING TO  SYSDBA;


SET TERM ^ ;
CREATE PROCEDURE AGETODAYSTRING (
    LDDAYOFBIRTH TIMESTAMP )
RETURNS (
    LCRESULT CHAR(250) )
AS
BEGIN
    EXECUTE PROCEDURE AgeGetString(ldDayOfBirth, CURRENT_TIMESTAMP, 'str') 
RETURNING_VALUES :lcResult;
    
    SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE AGETODAYSTRING TO  SYSDBA;


SET TERM ^ ;
CREATE PROCEDURE AGETODAYYEARSINT (
    LDDAYOFBIRTH TIMESTAMP )
RETURNS (
    LIRESULT INTEGER )
AS
DECLARE VARIABLE lcResult CHAR(250);

BEGIN
    EXECUTE PROCEDURE AgeGetString(ldDayOfBirth, CURRENT_TIMESTAMP, 'years') 
RETURNING_VALUES :lcResult;
    
    liResult = CAST(lcResult AS INTEGER);
    
    SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE AGETODAYYEARSINT TO  SYSDBA;


SET TERM ^ ;
CREATE PROCEDURE AGETODAYMONTHSINT (
    LDDAYOFBIRTH TIMESTAMP )
RETURNS (
    LIRESULT INTEGER )
AS
DECLARE VARIABLE lcResult CHAR(250);

BEGIN
    EXECUTE PROCEDURE AgeGetString(ldDayOfBirth, CURRENT_TIMESTAMP, 'months') 
RETURNING_VALUES :lcResult;
    
    liResult = CAST(lcResult AS INTEGER);
    
    SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE AGETODAYMONTHSINT TO  SYSDBA;


SET TERM ^ ;
CREATE PROCEDURE AGETODAYDAYSINT (
    LDDAYOFBIRTH TIMESTAMP )
RETURNS (
    LIRESULT INTEGER )
AS
DECLARE VARIABLE lcResult CHAR(250);

BEGIN
    EXECUTE PROCEDURE AgeGetString(ldDayOfBirth, CURRENT_TIMESTAMP, 'days') 
RETURNING_VALUES :lcResult;
    
    liResult = CAST(lcResult AS INTEGER);
    
    SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
 ON PROCEDURE AGETODAYDAYSINT TO  SYSDBA;