Description: Using LibreOffice Base, JDBC driver connected to a remote FireBird SQL database, a SQL query using firebird's DateAdd() function returns an unexpected end of statement error. An example query is: Select * From Entitlements Where EXPIRATIONDATE <= DateAdd(90 day to current_date) I was attempting to use this sql statement in a saved Query, and it tells me when attempting to run or save the query that the syntax is incorrect. However the query does execute properly within Firebird / within FlameRobin, a management tool for it. The query is accepted if I remove the Where clause that contains the DateAdd() function Steps to Reproduce: 1.Connect Base to an external FireBird database (Or, optionally, use an embedded database using Firebird) 2. Create a table in the database with a DATE column 3. Attempt to create a query using the firebird DateAdd() function Actual Results: SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE Expected Results: Query should have executed with appropriate data results Reproducible: Always User Profile Reset: No Additional Info: Version: 6.3.3.2 (x64) Build ID: a64200df03143b798afd1ec74a12ab50359878ed CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded
Have you tried setting the 'Run SQL Command Directly' control in the query window?
It will work right in direct SQL mode. It won't run with the query-GUI, because the query-GUI expects a mask like 'day' around the strings. You could use CAST( CURRENT_DATE AS DATE ) + 90 instead. Select * From Entitlements Where EXPIRATIONDATE <= CAST( CURRENT_DATE AS DATE ) + 90 Together with casting the current_date as date it will work in query-GUI, but will show the value as integer. So you have to format it afterwards.
The same issue exists/existed with mysql and the DATEADD function (and has done ever since I can remember, as I had to adapt my own queries to get this to work at the time). This could be seen as a limitation of the LO parser, but I have vague recollection that when I argued for this in the past, it was counter-argued that it would be impossible / too much work to implement and expect the parser to know all of the intricacies of each version of SQL specific to any given version of a DB engine. Setting as RESOLVED NOTABUG