0

I have the following code made from when my database was HSQLDB:

SELECT
    "Item",
    DATEDIFF('DAY', CURRENT_DATE, "Expiry") AS "Days Remaining"
FROM "Expirations"

The date values in the Expiry column are in the YY-MM-DD format.

After Libreoffice Base threw up a message about how it plans to eventually migrate to Firebird, I accepted the migration on a backup, which now causes the above query to throw up the following error:

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 26
*'DAY'
caused by
'isc_dsql_prepare'
at ./connectivity/source/drivers/firebird/Util.cxx:69

I only just started getting comfortable with the basics of HSQLDB, so I'm now trying to pour through the Firebird documentation to understand what the issue is.

The documentation states the correct syntax is:

DATEDIFF (<args>)

<args> ::=
    <unit> FROM <moment1> TO <moment2>
  | <unit>, <moment1>, <moment2>

<unit> ::=
    YEAR | MONTH | WEEK | DAY
  | HOUR | MINUTE | SECOND | MILLISECOND
<momentN> ::= a DATE, TIME or TIMESTAMP expression

DAY on its own throws up a syntax error, so I have to use 'DAY'. That wasn't the issue, so I'm not sure why it's having an issue with that aspect of the arguments. The Expiry column in that table was created as a date, and I would assume CURRENT_DATE is also a date.

What am I missing?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
hiigaran
  • 285
  • 1
  • 10

1 Answers1

1

The syntax for DATEDIFF in Firebird doesn't have the unit name in quotes, it is a bare token, not a string literal, so you need to use:

DATEDIFF(DAY, CURRENT_DATE, "Expiry")

or

DATEDIFF(DAY FROM CURRENT_DATE TO "Expiry")

Your problem seems specific to LibreOffice Base, and it looks like LibreOffice Base injects some sort of translation layer from HSQLDB syntax to Firebird syntax which doesn't handle this Firebird syntax and instead returns a syntax error. On the other hand, if you try to use the HSQLDB syntax, you run into a problem that the translation layer seems to evaluate CURRENT_DATE to a string literal (e.g. '2022-10-14' instead of just using CURRENT_DATE or providing a date literal like date '2022-10-14'), which then isn't accepted by Firebird (nor does the translation layer remove the quotes around 'day').

If you enable the "Run SQL command directly" option (button with word "SQL" and a green checkmark), the Firebird syntax works (but then HSQLDB-specific syntax will not work).

I recommend reporting a bug to LibreOffice.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Using `DAY` instead of `'DAY'` gives me a syntax error, and I cannot save the query to run it. And neither the comma variant, nor the From/To variant makes a difference. – hiigaran Oct 14 '22 at 08:42
  • 1
    @hiigaran It seems this is a bug in LibreOffice 7.4.2. When I posted my answer, I still had LibreOffice 7.3.0 installed, and in that version `select dateadd(day, current_date, date '2022-01-01') from rdb$database` works just fine, but in 7.4.2 it now indeed generates an unclear "syntax error" (which is not a Firebird error, but likely something thrown up by some intermediate layer in LibreOffice). – Mark Rotteveel Oct 14 '22 at 10:36
  • 1
    @hiigaran I meant `select datediff(day, current_date, date '2022-01-01') from rdb$database`, not `dateadd`. I think I see what I did different: in 7.3.0. I enabled the "Run SQL command directly" option and if I do the same in 7.4.2, then the query works. It seems that if you don't enable this, then LibreOffice will use a translation layer for the query, which doesn't quite work correctly. – Mark Rotteveel Oct 14 '22 at 10:44
  • Love it when you pull your hair out wondering where you went wrong, and it turns out to be something as silly as a bug. Running directly solved it. Cheers. – hiigaran Oct 16 '22 at 07:58