0

I am trying to compare the date modified (field type: Timestamp) with a value that is based off of a month before the current date. I keep getting a syntax error when using the function DateAdd().

I Am Using Libreoffice base 6.2.3.2 (x64) and firebird 3.0 embedded

Using this code, I get a result returning no records but no error

Select *
From "tblPart"
Where "Date Modified" = Current_Timestamp

but anytime I want to use the Dateadd() function, I get an error 

Select *
From "tblPart"
Where
   "Date Modified"< Dateadd(Month,-1,Current_Timestamp)

Expected to Return a list of results that have been modified over a month ago.

Errors:

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

//EDIT: Added the programs that I used

Arioch 'The
  • 15,799
  • 35
  • 62
aaron stevenson
  • 3
  • 4
  • 1
    None of those errors are generated by Firebird. Please provide a [mre] and describe how you are executing this (eg which tool, library, programming language, etc). – Mark Rotteveel Jul 09 '19 at 06:32
  • HY000 seems to be OpenOffice error about not being able to find JDBC driver or making connection https://ask.libreoffice.org/en/question/33841/base-error-message-connection-driver/ – Arioch 'The Jul 09 '19 at 08:25
  • 1
    @Arioch'The I'm not sure adding tags like openoffice-base without confirmation from the OP is the proper course of action here. HY000 is a standard SQL state for client errors. Firebird itself also uses it a lot (see https://github.com/FirebirdSQL/firebird/blob/master/src/msgs/system_errors2.sql which are the SQL state mappings that Firebird applies) – Mark Rotteveel Jul 09 '19 at 08:36
  • I still do not think you use Firebird. You perhaps do think so, but I do not think that really is what it is. Perhaps you use HSQLDB or something. Try query `SELECT * FROM RDB$DATABASE` - will it work? – Arioch 'The Jul 10 '19 at 08:34
  • One more possible reason that I thought of - I did not use OpenOffice Base so I am not sure about fine details of it - that the `DateAdd` function above if the function of OpenOffice not of Firebird, thus you have to add some screening, to prohibit OpenOffice from using its own function and just to pass the name as-is to the Firebird. https://help.libreoffice.org/Basic/DateAdd_Function_Runtime P.S. I am re-adding OOo tag, as LO and OOo are forks which are still rather close and basic questions probably intersect. – Arioch 'The Jul 10 '19 at 08:39
  • Dateadd is a function of firebird , I'm using the latest version of LO base with firebird 3.0 embedded – aaron stevenson Jul 10 '19 at 13:31

1 Answers1

0

I ran into the same problem. Though the SQL-statement with DateAdd() has been suggested as working, LO Base answered the query with a message box

Syntax error in SQL statement

Why it should work

In contrast the same SQL statement like in the query succeeded by running it in the "Execute SQL Statement" window (LO Base' main window menu "tools" > "SQL…").

Solution

What finally got my query to work was to check "Run SQL command directly" in the toolbar or in the "Edit" menu. This prevents LO from analyzing the SQL query before execution. This fails, because it is not understanding the full SQL statement (firebird's DateAdd()-function), and thus is the reason for the mentioned errors.

Qny
  • 83
  • 2
  • 5