CREATE OR ALTER FUNCTION AGE(DFROM TIMESTAMP, DTO TIMESTAMP)
RETURNS VARCHAR(30)
AS
BEGIN
RETURN
CASE
WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
THEN datediff(year, :DFROM, :DTO)-1
ELSE datediff(year, :DFROM, :DTO)
END || 'y ' ||
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 || 'm ' ||
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 || 'd';
END;
---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), 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.