如何在LibreOffice Base的HSQLDB查询中为日期添加天数？

算力阿峰

2026-7-18

在LibreOffice Base（嵌入式HSQLDB）中计算日期后30天的到期日

我正在使用带有嵌入式HSQLDB数据库的LibreOffice Base，现有查询语句如下，用于从表中提取单行数据：

SELECT "ID", "Name", "Date" FROM "Table" WHERE "ID" = ?

我希望添加一个“Due Date”字段，显示“Date”字段日期后30天的日期，但尝试多种方法均未成功，以下是尝试过的语句及对应的错误信息：

..., "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", ...
无报错但无输出
..., 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

编辑1：
此问题并非类似问题的重复，我在第4和第6次尝试中已使用INTERVAL语法但未成功。

编辑2：
22. ..., "Date" + INTERVAL '30' DAYS AS "Due Date", ...
错误：Syntax error in SQL statement
23. ..., "Date" + 30 * INTERVAL '1' DAYS AS "Due Date", ...
错误：Syntax error in SQL statement
24. ..., "Date" + INTERVAL '1' MONTH AS "Due Date", ...
错误：Syntax error in SQL statement
25. ..., "Date" + (INTERVAL '1' MONTH) AS "Due Date", ...
错误：Syntax error in SQL statement
26. ..., "Date" + (INTERVAL '1' DAY * 30) AS "Due Date", ...
错误：Syntax error in SQL statement

内容的提问来源于stack exchange，提问作者18107