CREATE OR ALTER FUNCTION AGE(DFROM TIMESTAMP, DTO TIMESTAMP=CURRENT_DATE, 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;
IF (m<0) THEN
BEGIN
y = CAST(y AS integer) - 1;
m = CAST(m AS integer) + 12;
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;
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.