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
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)