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;