How can I add days to a date field in base query (with firebird backend)

[BequiaFella]

I’m trying to write a query that needs me to do some date arithmetic. I just need something simple: to add 7 days to the current date and compare that with a date field in the table. I can get my query to work if I run the SQL command directly but that causes problems later for generating reports. Therefore I need to fix it so that it works without ‘run query directly’.

• DATEADD(), DATEDIFF(), CURRENT_DATE and NOW…etc aren’t recognised.
• I’ve tried to follow the HSQLDB and Firebird syntax without any success.

how do you get this thing to work correctly?

• IMPORTANT: it’s a Firebird db

[Ratslinger]

Hello,

The Query editor was designed for HSQLDB embedded. It does some verification that syntax is correct based on that database. With other databases using even standards not in the HSQLDB v1.8 DB, it is necessary to turn on Run SQL command directly in order to bypass this checking.

So the question becomes what problem with generating reports are you having? What specific version of LO are you using? OS? What Firebird - Server (version) or embedded?

[BequiaFella]

Thanks for the reply Ratslinger,
That bit about the editor expecting HSQLDB is quite confusing for someone who isn’t aware of all these details of what’s happening in the background. The problem I was having with the reports was that whenever I tried to generate the reports, an error would pop up saying that the query couldn’t be processed. Later during the day while fiddling around with various settings I found the solution to the report problem:

• Go to the report properties in the right pane → data tab → Analyze SQL Command, change from YES to NO so that it runs the query through the database backend , which is equivalent to " Run SQL command directly" in the query editor.

[BequiaFella]

I eventually got the report working, with a little help. Here’s the run-down:

The query editor GUI was originally meant to work with HSQLDB, which does not implement some of the functions you would be accustomed to in SQL Server and others. As of LibreOffice 7 (which is what I’m using at the time of writing), even if you enable the Firebird embedded database feature instead of using the old HSQLDB back-end, it still expects HSQL syntax. Some complicated queries can’t be created graphically but work when you write it in the SQL view and click run. And then there are yet some that require you to make use of SQL commands native to Firebird or whatever database you’re using. In those cases you need to select Run SQL Directly. The same applies to reports, so you need to go to the properties in the right hand pane. First click somewhere in the blank gray area at the bottom to make to pane show the “report properties” and then in the Data tab, look for Analyze SQL Command. Set that to NO, so that it lets Firebird process the query instead.

Date And Time Examples

HSQLDB and Firebird have different functions for date and time here’s some of what I used. Try not to get them mixed up.

– WITH FIREBIRD Embedded–

For getting the current date, use this sub-query: SELECT CAST('Today' AS DATE) FROM rdb$database
For getting the current week number: EXTRACT(WEEK FROM (SELECT CAST('Today' AS DATE) FROM rdb$database))
To check if some date in a table is in the current week something like this should do: EXTRACT(WEEK FROM "dateSched") = EXTRACT(WEEK FROM (SELECT CAST('Today' AS DATE) from rdb$database))

– WITH HSQLDB Embeded–
DATEDIFF() and NOW() work, so you can check for an interval between dates like this
WHERE DATEDIFF('DAY',NOW(), "dateSched" )<7 AND DATEDIFF('DAY',NOW(), "dateSched" )>=0

SEE HERE FOR MORE DIFFERENCES
HOW TO MIGRATE FROM HSQLDB

[Ratslinger]

There is also for Firebird CURRENT_DATE; CURRENT_TIME; CURRENT_TIMESTAMP without the need to CAST. Can also cast NOW in Firebird.

See also some other comparisons of HSQLDB vs Firebird → Firebird Migration Expectations

Lots more in documentation → Firebird RDBMS