Page 1 of 1

[Solved] DateCalc HSQL2.5 backend or H2 backend

Posted: Thu Aug 29, 2019 5:56 pm
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?
GK

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Thu Aug 29, 2019 6:15 pm
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.

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Thu Aug 29, 2019 8:01 pm
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.

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Thu Aug 29, 2019 10:42 pm
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

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 11:48 am
Searching for keywords "Birthday Age" in the Base forum: viewtopic.php?f=13&t=36069&p=412463&hilit=birthday+age#p412463

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 3:52 pm
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 valuesCREATE 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 valuesCREATE 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.TABLESWhere 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.

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 4:23 pm
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.TABLESWhere INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = CURRENT_SCHEMAOrder By INFORMATION_SCHEMA.TABLES.tblContacts ASC]`

Do I need to put the table name in quotes?

kind regards
GK

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 4:43 pm
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.TABLESWhere 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.

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 4:55 pm
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!

Re: DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 5:09 pm
gkick wrote: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.

Re: [Solved]DateCalc HSQL2.5 backend or H2 backend

Posted: Fri Aug 30, 2019 5:20 pm
Thank you!