[Solved] DateCalc HSQL2.5 backend or H2 backend
[Solved] DateCalc HSQL2.5 backend or H2 backend
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
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
Last edited by gkick on Fri Aug 30, 2019 5:19 pm, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: DateCalc HSQL2.5 backend or H2 backend
HSQL 2.5:
selects all fields plus a calculated date field "+15 months" not older than 30 days.
Code: Select all
SELECT "Table".*, dateadd( 'mm', 15, "D" ) AS "+15 months" FROM "Table" WHERE DATEDIFF( 'dd', "D", CURRENT_DATE ) <= 30
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: DateCalc HSQL2.5 backend or H2 backend
Another example converting German banking dates, imported as text from csv, into valid date values:
"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.
Code: Select all
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"
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: DateCalc HSQL2.5 backend or H2 backend
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
mfg
GK
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
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
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: DateCalc HSQL2.5 backend or H2 backend
Searching for keywords "Birthday Age" in the Base forum: viewtopic.php?f=13&t=36069&p=412463&hil ... ge#p412463
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: DateCalc HSQL2.5 backend or H2 backend
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:
From the OpenOffice / LibreOffice Base File ( *.odb ) Menu: Tools -> SQL...
So, try following Query, to see how it works:
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.
- 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
From the OpenOffice / LibreOffice Base File ( *.odb ) Menu: Tools -> SQL...
Code: Select all
-- 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
-- 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
Code: Select all
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
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
Thank you for your help Sliderule, very much appreciated. Loaded both functions via Tools, SQL, however running the query produces an error message.
Do I need to put the table name in quotes?
kind regards
GK
Code: Select all
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]
kind regards
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: DateCalc HSQL2.5 backend or H2 backend
Below is just a SAMPLE Query.
To run it with a Query from YOUR TABLES, and, COLUMN NAME, this is an Example:
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.
Code: Select all
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
Code: Select all
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"
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Fri Aug 30, 2019 5:09 pm, edited 1 time in total.
Re: DateCalc HSQL2.5 backend or H2 backend
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!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: DateCalc HSQL2.5 backend or H2 backend
Yes, you can use it, like any other HSQL DATABASE function.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!
For EXAMPLE:
Code: Select all
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"
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
Thank you!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend