Can you use DATE_ADD in a Query, either Design or SQL? I’m using Libreoffice Base 25.2.5.2 and a Mariadb 11.4.7 database btw. I’m trying to add a number of years (integer column from database table) to a date from the same database table. btw, it also didn’t work when I was using a straight Libreoffice database either, so I don’t know what I’m doing wrong. I see “DATE_ADD(date, INTERVAL value unit)” and I see DATE_ADD( ‘Date’, INTERVAL ‘Duration’ YEAR). I always get Syntax error in SQL statement. I could do this in Microsoft Access. I need some method of doing this. Thanks.
DATE_ADD in MariaDB will only work when direct SQL will be executed. In query editor press the button for direct SQL.
DATE_ADD is only implemented for internal Firebird. Internal HSQLDB is so old it doesn’t know anything about this function.
My actual database is in MariaDB. I’m sure it supports something like DATE_ADD or DATEADD, but don’t know how to implement it yet. I assume it’s something easy and obvious that I am missing.
RobertG, I am unfamiliar with direct SQL, I am barely familiar with SQL. I’m assuming that direct SQL is a way of passing SQL directly to MariaDB rather than having Libreoffice Syntax check it.
Exactly and there is a switch in the query editor for sql ( rightmost icon in the toolbar tp select it.
Then you can write all sql MariaDB can interpret.
See SQL pressed in query editor.
1 Like
RobertG - OK, I’m still not getting it to work, but… in your example you say INTERVAL 1 Day, but instead of 1 can you put in a column name, like if you had Datum_Dur (or whatever) with an integer in it (a number of years, like 1 year membership or 3 year membership, would that work. Because I keep getting a syntax error and that I should check the documentation for my Mariadb server version
Your syntax error will be immediately before the part of the query quoted in the error message.
You have 'dues_duration YEAR' so MariaDB will expect YEAR as part of column name and miss the keyword YEAR after the column. Therefore syntax-error.
Try "dues_duration" YEAR
In parsed mode (not direct SQL), the following works for me:
SELECT `author`.`birth`,
cast(concat(Year( `birth`)+ :param_Years,'-', Month(`birth`),'-', Day(`birth`)) AS DATE) `Plus Param Years`
FROM `dummy`.`author` `author`
Instead of :param_Years, you may use a column name providing the integer.
Thanks, I’ll give it a try. Need to get it by the end of the month (hopefully)
@jfervin : You know the problem of your code is a problem of right quoting? Single quoting 'text' will be used for text content. Double quoting "table" or "field" or "database" will be used for fields and so on. In MariaDB and MySQL is also used a backtic symbol. You have set in you codes instead of backtic many single quotes.
1 Like
It appears that Libreoffice uses single quotes in SQL queries for fields, as in
SELECT Dues_Number, Dues_Date_Paid, MAX( Dues_Begin_Date ), Dues_Duration FROM CIAMembership.Dues Dues GROUP BY Dues_Number ORDER BY Dues_Number ASC, ‘Dues_Begin_Date’ ASC
Are you suggesting that I should change the quotes to double quotes? BTW, if you switch something to double quotes, and save it, libreoffice will switch it back to single quotes.
" → Double quotes, used normally for field names, tables names …
` → Backtic, used for MariaDB/MySQL for field names, table names …
' → Single quotes, only used for text content
LibreOffice uses backtics for MariaDB. Don’t use single quotes as I could see in your screenshot above. Quote all in backtics or double quotes, which might be set to backtic when opening the query for editing again.
Thank you all for your help, I’d about given up. The solution seems to have been:
SELECT Dues_Begin_Date, DATE_ADD(Dues_Begin_Date, INTERVAL Dues_Duration YEAR) AS ‘ExpDate’ FROM Dues;
Or removing the single quotes from around the column names.
It’s always the same, you have a problem, you beat your head on the wall until a solution occurs. I mean none of the examples I say even implied that you can use column names, they always had integers and hard coded dates.
This will be the code inside the query editor:
SELECT `Dues_Begin_Date`,
DATE_ADD(`Dues_Begin_Date`, INTERVAL `Dues_Duration` YEAR) AS `ExpDate`
FROM `Dues`
Fields and tables could also been set in default double quotes:
SELECT "Dues_Begin_Date",
DATE_ADD("Dues_Begin_Date", INTERVAL "Dues_Duration" YEAR) AS "ExpDate"
FROM "Dues"
Single quotes are used to write text content in a query.
That’s why there is given syntax: You can put an expression there:
DATE_ADD(date,INTERVAL expr unit)
You know, the weird thing is that it seems oblivious to single vs double quotes, but it doesn’t allow quotes at all in the DATE_ADD function. What works is:
DATE_ADD(Dues_Begin_Date, INTERVAL Dues_Duration YEAR)
with no quotes.
Also, noticed that there are three types of quotes, ‘single quotes’, “double quotes”, and and accent quote. Libreoffice seems to use the ``````` quote. But still not in the DATE_ADD function where is doesn’t seem to allow any quotes (DATE_ADD(Dues_Begin_Date, INTERVAL Dues_Duration YEAR)
While in direct SQL mode, the rules of your database engine do apply. MySQL quotes names in back ticks. If a name has no spaces nor special characters and if it does not confilct with any SQL keyword, the quotes can be omitted. Direct SQL record sets do not work with pairs of form and subform. A direct sql subform is not filtered by its parent. A direct sql form does not filter any of its childs. Direct mode result sets are read-only always.
While in parsed mode, LibreOffice imposes its own rules with double-quoted names and single quoted strings. When Base parses a query against MySQL, it changes double-quotes into backticks. You can use both. In parsed mode, LibreOffice has somewhat “full control” on the record set. All the Base features (mainly form/subform and parameters) do work in parsed mode. Record sets are editable if they include a single table including its primary key.