[Solved] DateCalc HSQL2.5 backend or H2 backend

[gkick]

Hi,

My contacts table has several fields for date alerts like
Flagtype LookupTable, Birthday date datatype, Flag boolean, RemindMePriorTo integer
What I would like to achieve is to show a set like...

FlagType Contact NextBD Turning FlagDate
Birthday Joe Blow 17/01/2020 62 NextBD-days priorto

Having problems using the value of a calculated field in a secondary calculation (unknown field errors)

Another more tricky one is the FollowUp for contact where there is only a boolean follow up field and a in x day field, the idea being follow up in 20 days from now, guess this could be done with a timestamp in a dummy table or putting CURDATE() into some string where now will remain static?
Thanks for your thoughts.
GK

[Villeroy]

HSQL 2.5:

Code: Select all   Expand viewCollapse view

SELECT "Table".*, dateadd( 'mm', 15, "D" ) AS "+15 months" FROM "Table" WHERE DATEDIFF( 'dd', "D", CURRENT_DATE ) <= 30

selects all fields plus a calculated date field "+15 months" not older than 30 days.

[Villeroy]

Another example converting German banking dates, imported as text from csv, into valid date values:

Code: Select all   Expand viewCollapse view

SELECT CAST(REPLACE(REPLACE(SUBSTR("VALUTA",7,4)||'-'||SUBSTR("VALUTA",4,2)||'-'||SUBSTR("VALUTA",1,2),'-02-29','-02-28'),'-02-30','-02-28')AS DATE)AS "DATUM"

"VALUTA" has German date strings like 03.12.2018 including invalid February dates 29.2. and 30.2. because the banksters calculate with months of 30 days.
The expression replaces 29.2. and 30.2. with 28.2. (which is OK for my purposes) and concatenates 03.12.2018 to ISO date 2018-12-03 which can be converted to a true date value by means of CAST(ISO_Date_String AS DATE).
Calculated dates may show up as integers in the Base GUI. This is not a problem. Just format these day numbers when they appear in form controls or report cells.

[gkick]

Thank you very much Villeroy,

I am almost getting there,

I need to modify the code below that the Age will be the Age on the next Birthday, in the case of Kick it would be 62 as January has already passed, same with the nextdb calculation which should show 17/01/2020

Code: Select all   Expand viewCollapse view

SELECT "LastName", "DateOfBirth", "nPrior", "BdR",YEAR( NOW( ) ) - YEAR( "DateOfBirth" ) as "Age",DATEADD(YEAR,DATEDIFF(YEAR,"DateOfBirth",CURDATE()),"DateOfBirth")
AS "nextdb" FROM "tblContacts" WHERE "BdR" = TRUE


mfg
GK

[Villeroy]

Searching for keywords "Birthday Age" in the Base forum: viewtopic.php?f=13&t=36069&p=412463&hilit=birthday+age#p412463

[Sliderule]

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.

[gkick]

Thank you for your help Sliderule, very much appreciated. Loaded both functions via Tools, SQL, however running the query produces an error message.

Code: Select all   Expand viewCollapse view

unexpected token: AGE_FLOAT : line: 8 in statement [Select
   INFORMATION_SCHEMA.TABLES.tblContacts,
   AGE(NULL, CURRENT_DATE) as "AGE_NULL",
   AGE( CAST(TO_DATE('30.12.19', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_CUR_DATE",
   AGE("DateOfBirth", CURRENT_DATE) as "DateOfBirth",
   AGE_FLOAT(NULL, CURRENT_DATE) as "AGE_FLOAT_NULL",
   AGE_FLOAT( CAST(TO_DATE('30.12.20', 'DD.MM.YY') as DATE), CURRENT_DATE) as "AGE_FLOAT_CUR_DATE"
   AGE_FLOAT("DateOfBirth", CURRENT_DATE) as "AGE_FLOAT_BIRTH_DATE",
From INFORMATION_SCHEMA.TABLES
Where INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = CURRENT_SCHEMA
Order By INFORMATION_SCHEMA.TABLES.tblContacts ASC]

Do I need to put the table name in quotes?

kind regards
GK

[Sliderule]

Below is just a SAMPLE Query.

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( {D '1999-12-30'}, 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({D '1999-12-30'}, 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


To run it with a Query from YOUR TABLES, and, COLUMN NAME, this is an Example:

Code: Select all   Expand viewCollapse view

Select
   "MY_TABLE_NAME".*,
   AGE("MY_TABLE_NAME"."BIRTH_DATE", CURRENT_DATE) as "AGE_INTEGER",
   -- This Returns integar with 13 decimals
   AGE_FLOAT("MY_TABLE_NAME"."BIRTH_DATE", CURRENT_DATE) as "AGE_FLOAT"
From "MY_TABLE_NAME"


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.

[gkick]

Yuhuu, thanks a million, works like a charm, now could I use a similar function to return the date of my next birthday which I need for another query to filter upcoming birthdays. My bd is 17/01/1958,since we are now in September the query should show 17/01/2020 as the next birthday without hard coding next year. Thanks a lot!

[Sliderule]

Yuhuu, thanks a million, works like a charm, now could I use a similar function to return the date of my next birthday which I need for another query to filter upcoming birthdays. My bd is 17/01/1958,since we are now in September the query should show 17/01/2020 as the next birthday without hard coding next year. Thanks a lot!

Yes, you can use it, like any other HSQL DATABASE function.

For EXAMPLE:

Code: Select all   Expand viewCollapse view

Select
   "MY_TABLE_NAME".*,
   AGE("MY_TABLE_NAME"."BIRTH_DATE", DATEADD('month',3,CURRENT_DATE) ) as "AGE_INTEGER",
   -- This Returns integar with 13 decimals
   AGE_FLOAT("MY_TABLE_NAME"."BIRTH_DATE", DATEADD('month', 3, CURRENT_DATE) ) as "AGE_FLOAT"
From "MY_TABLE_NAME"


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.

[gkick]

Thank you!