I'm using libreoffice base with an embedded HSQLDB database. I have a query extracting a single line from a table.
SELECT "ID", "Name", "Date" FROM "Table" WHERE "ID" = ?
I would like to add a field "Due Date" that displays a date 30 days after "Date".
So far I have tried:
..., "Date" + 30 AS "Due Date", ... Wrong date type: java.lang.NumberFormatException
..., DATEADD(D, 30, "Date") AS "Due Date", ... Syntax error in SQL statement
..., DATEADD("d", 30, "Date") AS "Due Date", ... Access is denied: DATEADD in statement
..., DATEADD("Date", INTERVAL 30 DAY) AS "Due Date", ... Syntax error in SQL statement
..., "Date" + TO_DATE( 30 ) AS "Due Date", ... Access is denied: TO_DATE in statement
..., "Date" + INTERVAL 30 DAYS AS "Due Date", ... Syntax error in SQL statement
..., CAST("Date" AS INT) AS "Due Date", ... Wrong data type: java.lang.NumberFormatException
..., CAST("Date" AS FLOAT) AS "Due Date", ... Wrong data type: java.lang.NumberFormatException
..., CONVERT(INT, "Date") AS "Due Date", ... Syntax error in SQL statement
..., CONVERT('int', "Date") AS "Due Date", ... Wrong data type: Date in statement
..., DATEDIFF(DAY, '1899-12-30T00:00:00', "Date") AS "Due Date", ... Syntax error in SQL statement
..., DATEDIFF('1899-12-30T00:00:00', "Date") AS "Due Date", ... No error, but no output either
..., DATEDIFF('d', '1899-12-30T00:00:00', "Date") AS "Due Date", ... Wrong data type: java.lang.IllegalArgumentException
..., CAST( ( DATEDIFF( 'day', '1899-12-30 00:00:00', "Date" ) + 30 ) AS DATE ) AS "Due Date", ... Wrong data type: java.lang.IllegalArgumentException
..., CAST( ( DATEDIFF( 'day', '1899-12-30 00:00:00', "Date" ) + 30 ) AS DATETIME ) AS "Due Date", ... Wrong data type: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
..., "Date" + DAYS( '1900-01-30 00:00:00' ) AS "Due Date", ... Access is denied: DAYS in statement
..., "Date" + DAY( '1900-01-30 00:00:00' ) AS "Due Date", ... Wrong data type: java.lang.NumberFormatException
..., "Date" + '1900-01-30 00:00:00' AS "Due Date", ... Syntax error in SQL statement
..., "Date" + CAST( '1900-01-30 00:00:00' AS DATE ) AS "Due Date", ... Wrong data type: java.lang.NumberFormatException
..., CAST( "Date" AS "DATETIME" ) + CAST( '1900-01-30 00:00:00' AS "DATETIME" ) AS "Due Date", ... Wrong data type: java.lang.NumberFormatException
..., CAST( YEAR( "Date" ) || '-' || MONTH( "Date" ) || '-' || ( DAY( "Date" ) + 30 ) AS "DATE" ) AS "Due Date", ... Wrong data type: java.lang.IllegalArgumentException
EDIT 1:
This is not a duplicate of Add Interval to date in HSQLDB as I have not been successful in using INTERVAL as demonstrated in my 4th and 6th attempts.
EDIT 2:
22. ..., "Date" + INTERVAL '30' DAYS AS "Due Date", ... Syntax error in SQL statement
..., "Date" + 30 * INTERVAL '1' DAYS AS "Due Date", ... Syntax error in SQL statement
..., "Date" + INTERVAL '1' MONTH AS "Due Date", ... Syntax error in SQL statement
..., "Date" + (INTERVAL '1' MONTH) AS "Due Date", ... Syntax error in SQL statement
..., "Date" + (INTERVAL '1' DAY * 30) AS "Due Date", ... Syntax error in SQL statement
In HSQLDB 1.8.0 there is no equivalent function. See the list of supported functions here:
https://hsqldb.org/doc/1.8/guide/ch09.html#N1251E
You can use a LibreOffice extension to enable the use of the latest HSQLDB 2.x with LibreOffice:
https://hsqldb.org/doc/2.0/guide/openoffice-app.html#ooa_extensions