If you are using an HSQL database, that is
NOT an
Embedded HSQL NOR an
External HSQL that is Version
1.8.0.10, you can easily write your own
AGE function(s). Below are examples:
- AGE that returns an INTEGER, with TWO DATE inputs: STARTDATE and ENDDATE
- AGE_FLOAT that returns a FLOAT NUMBER, with TWO DATE inputs: STARTDATE and ENDDATE
The function(s) are created once for the database, and, available afterwords with any Query, Insert, Update, Merge, Delete statement.
From the OpenOffice / LibreOffice Base File (
*.odb ) Menu:
Tools -> SQL...- Code: Select all Expand viewCollapse view
-- Below function calculates an AGE in YEARS by input of two Database Date values
CREATE FUNCTION AGE(IN STARTDATE DATE, IN ENDDATE DATE)
RETURNS INTEGER
LANGUAGE SQL NOT DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
RETURN YEAR(ENDDATE) - YEAR(STARTDATE) - CASE WHEN MONTH(ENDDATE)*100+DAYOFMONTH(ENDDATE)>=MONTH(STARTDATE)*100+DAYOFMONTH(STARTDATE)THEN 0 ELSE 1 END
- Code: Select all Expand viewCollapse view
-- Below function calculates an AGE in YEARS, as a FLOAT ( integer with decimal places ) by input of two Database Date values
CREATE FUNCTION AGE_FLOAT(IN STARTDATE DATE, IN ENDDATE DATE)
RETURNS DOUBLE
LANGUAGE SQL NOT DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
RETURN CASE WHEN TO_CHAR(ENDDATE,'MMDD')=TO_CHAR(STARTDATE,'MMDD')THEN YEAR(ENDDATE)-YEAR(STARTDATE)WHEN YEAR(ENDDATE)=YEAR(STARTDATE)AND TO_CHAR(ENDDATE,'MMDD')<TO_CHAR(STARTDATE,'MMDD')THEN CAST(YEAR(ENDDATE)-YEAR(STARTDATE)+1-(DAYOFYEAR(STARTDATE)*1.0E0/DAYOFYEAR(ENDDATE))AS FLOAT)WHEN YEAR(ENDDATE)>YEAR(STARTDATE)THEN CAST(YEAR(ENDDATE)-YEAR(STARTDATE)-((DAYOFYEAR(STARTDATE)-DAYOFYEAR(ENDDATE))/365.0E0)AS FLOAT)WHEN YEAR(ENDDATE)=YEAR(STARTDATE)THEN CAST(((DAYOFYEAR(ENDDATE)-DAYOFYEAR(STARTDATE))/365.0E0)AS FLOAT) END
So, try following Query, to see how it works:
- Code: Select all Expand viewCollapse view
Select
INFORMATION_SCHEMA.TABLES.TABLE_NAME,
AGE(NULL, CURRENT_DATE) as "AGE_NULL",
AGE( CAST(TO_DATE('30.12.99', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_CUR_DATE",
AGE("MY_BIRTH_DATE_COLUMN", CURRENT_DATE) as "AGE_BIRTH_DATE",
AGE_FLOAT(NULL, CURRENT_DATE) as "AGE_FLOAT_NULL",
AGE_FLOAT( CAST(TO_DATE('30.12.99', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_FLOAT_CUR_DATE"
AGE_FLOAT("MY_BIRTH_DATE_COLUMN", CURRENT_DATE) as "AGE_FLOAT_BIRTH_DATE",
From INFORMATION_SCHEMA.TABLES
Where INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = CURRENT_SCHEMA
Order By INFORMATION_SCHEMA.TABLES.TABLE_NAME ASC
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.