Firebird:Age Calculation on Ymd

CREATE OR ALTER FUNCTION AGE(DFROM TIMESTAMP, DTO TIMESTAMP, CODED BOOLEAN = FALSE) RETURNS VARCHAR(30) AS DECLARE y varchar(3); DECLARE m varchar(2); DECLARE d varchar(2); BEGIN y = CASE WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365 THEN datediff(year, :DFROM, :DTO)-1 ELSE datediff(year, :DFROM, :DTO) END; m = CASE WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365 AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0 THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO)-1, :DFROM), :DTO)-1 WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365 AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)>=0 THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO)-1, :DFROM), :DTO) WHEN datediff(year, :DFROM, :DTO) = datediff(day, :DFROM, :DTO)/365 AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0 THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO), :DFROM), :DTO)-1 ELSE datediff(month, dateadd(year, datediff(year, :DFROM, :DTO), :DFROM), :DTO) END; d = CASE WHEN datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0 THEN datediff(day, dateadd(month, datediff(month, :DFROM, :DTO)-1, :DFROM), :DTO) ELSE datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO) END; RETURN CASE WHEN :CODED THEN lpad(Y,3,'0')||'-'||lpad(m,2,'0')||'-'||lpad(d,2,'0') ELSE Y||'y '||m||'m '||d||'d' END; END;
SELECT AGE(CAST('1971-04-13' AS DATE), CAST('NOW' AS DATE), TRUE) FROM RDB$DATABASE;
SELECT AGE(CAST('1971-04-13' AS DATE), CAST('NOW' AS DATE)) FROM RDB$DATABASE;
SELECT AGE(CAST('1971-04-13' AS DATE), CAST('NOW' AS DATE), FALSE) FROM RDB$DATABASE;

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.