How do I add days to a date in a query?

Question

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

[Add Interval to date in HSQLDB](https://stackoverflow.com/questions/19009143/add-interval-to-date-in-hsqldb) — Ponder Stibbons, Jun 17 '23 at 11:15
I have tried that solution and it didn't work. I got a syntax error (see attempt 6) and I don't know why. Are you able to provide a working example? — 18107, Jun 17 '23 at 18:51
I don't use HSQLDB, but mentioned answer and [this answer](https://stackoverflow.com/questions/34650406/add-interval-to-date-in-hsqldb-based-on-other-column?rq=3) suggests syntax: `"Date" + INTERVAL '30' DAYS` or `"Date" + 30 * INTERVAL '1' DAYS` which is common in other environments, for example in Oracle ([dbfiddle](https://dbfiddle.uk/kd1J2w7G)), Postgres ([dbfiddle](https://dbfiddle.uk/L-0A728H)). Just add apostrophes. — Ponder Stibbons, Jun 17 '23 at 20:14
Thanks for the help. Unfortunately no luck with either of those. It just says Syntax error, and doesn't specify with part of the statement is wrong — 18107, Jun 17 '23 at 22:45
This question is about HSQLDB 1.8.0 in LibreOffice which does not have the advanced date/interval features of HSQLDB 2.x — fredt, Jun 19 '23 at 16:51
Can you give the full text of your query #22? Including trailing semicolon and any code used to replace the "?"? It sounds like there may be a syntax error elsewhere in the query. — Matthias Fripp, Jun 19 '23 at 17:05
22. `SELECT "ID", "Name", "Date", "Date" + INTERVAL '30' DAYS AS "Due Date" FROM "Table" WHERE "ID" = ?` ? is user input (integer) — 18107, Jun 20 '23 at 21:55
@18107, I would recommend trying that query without the `INTERVAL` calculation to see if it works, but I suspect it will work just fine. As @fredt mentioned LibreOffice is bundled with HSQLDB 1.8 which doesn't have this feature. They say you can [drop in a later version](https://hsqldb.org/doc/2.0/guide/openoffice-app.html) but that won't work with embedded databases, so it seems like you are out of luck. — Matthias Fripp, Jun 23 '23 at 01:36
For what it's worth, ChatGPT suggests you can use `SELECT {fn TIMESTAMPADD(SQL_TSI_DAY, 30, date_column)} AS modified_date FROM your_table;` in HSQLDB 1.8, and that "the syntax {fn ...} is specific to HSQLDB 1.8 and is used to invoke functions that are not directly supported by the database engine." But I suspect it is hallucinating. Its first suggestion was to use `DATE_ADD(col, INTERVAL 30 DAY)` but when I pressed it, it admitted that function didn't exist in HSQLDB 1.8. Then we went in a loop of "sorry" and different answers, which is usually what happens when there's no good answer. — Matthias Fripp, Jun 23 '23 at 01:52

score 2 · Accepted Answer · answered Jun 22 '23 at 16:52

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

score 1 · Answer 2 · answered Jun 25 '23 at 03:12

I found a solution that works for my needs, but it's far from elegant. It is hard-coded to add 30 days, and adds 31 days if February 29th is in the 30 day range.

SELECT "ID", "Name", "Date" AS "Created Date", 
CASE
    WHEN MONTH("Date") = 2 THEN
        CAST(YEAR("Date") || '-' || MONTH("Date") + 1 || '-' || DAY("Date") + 2 AS DATE)
    WHEN MONTH("Date") IN (4, 6, 9, 11) THEN
        CAST(YEAR("Date") || '-' || MONTH("Date") + 1 || '-' || DAY("Date") AS DATE)
    ELSE
        CASE
            WHEN DAY("Date") = 1 THEN
                CAST(YEAR("Date") || '-' || MONTH("Date") || '-' || DAY("Date") + 30 AS DATE)
            WHEN MONTH("Date") = 12 THEN
                CAST(YEAR("Date") + 1 || '-' || '1' || '-' || DAY("Date") - 1 AS DATE)
            WHEN MONTH("Date") = 1 THEN
                CASE
                    WHEN DAY("Date") < 30 THEN
                        CAST(YEAR("Date") || '-' || MONTH("Date") + 1 || '-' || DAY("Date") - 1 AS DATE)
                    ELSE
                        CAST(YEAR("Date") || '-' || MONTH("Date") + 2 || '-' || DAY("Date") - 29 AS DATE)
                END
            ELSE
                CAST(YEAR("Date") || '-' || MONTH("Date") + 1 || '-' || DAY("Date") - 1 AS DATE)
        END
END
AS "Due Date" FROM "Table" WHERE "ID" = ?

score 0 · Answer 3 · answered Jun 23 '23 at 07:19

0

Try this one:

SELECT "ID", "Name", "Date", DATEADD('DAY', 30, "Date") AS "Due Date"
FROM "Table"
WHERE "ID" = ?

answered Jun 23 '23 at 07:19

lareb

98
6

How do I add days to a date in a query?

3 Answers3