Page 1 of 1
[Solved] Problem with date_add function
Posted: Thu Jan 11, 2018 7:12 am
by assa
I want to use date_add function for which i have written a query as follows:
Code: Select all
select "p_birthdate", DATE_ADD("p_birthdate", INTERVAL 3 MONTH) from "tbl_person";
Here p_birthdate is my field name which stores birth date and i want to add 3 months to each date.
When I run this query I get Syntax Error in SQL Expression.
I don't know what is wrong with this query.
Please Help!
Re: problem with date_add function
Posted: Thu Jan 11, 2018 7:37 am
by Sliderule
Prior to you SQL Query being sent to the database back-end ( I assume you are using HSQL Version 2.3.? or 2.4.? ,
NOT an Embedded Database ) . . . OpenOffice / LibreOffice Base will 'parse' - AKA
Base Parser - read the Query and make changes ( for example formatting of dates, or, prompt the user for input ). Unfortunately, the Base Parser will not accept ( allow ) that function ( actually, the INTERVAL part ).
Solution:
- Use the HSQL Function DATEADD instead. The syntax for your Query would be:
Code: Select all
select
"p_birthdate",
DATEADD('month', 3, "p_birthdate"
from "tbl_person"
- For the code exactly as you wrote it, prevent the Base Parser from making any changes to the Query, and, run the Query directly. Either
- From the Menu: Edit -> Run SQL Command directly[ should be checked
- On the Query Toolbar, click on the icon SQL with a green check mark.
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add
[Solved] in your
1st post Subject (edit button top right) if this issue has been resolved.
Re: problem with date_add function
Posted: Thu Jan 11, 2018 11:41 am
by assa
Thanks for the reply.
But my problem still continues. I tried both the solutions but no result.
Re: problem with date_add function
Posted: Thu Jan 11, 2018 12:02 pm
by robleyd
If you are getting an error message, please post your query and the exact content of the message. Also, are you using the embedded database or something else?
Re: Problem with date_add function
Posted: Thu Jan 11, 2018 4:46 pm
by Sliderule
Since you are using documentation from a database back-end that you are not using ( HSQL Embedded Database is HSQL Version 1.8.0.10 ) and you are quoting documentation and functions from HSQL 2 ( HSQL 2.4.0 as of the date I am writing this ), the functions, DATE_ADD and DATEADD are not available.
Do a search in this forum for the words DATEADD HSQL .
If it were me, I would simply use a SPLIT database ( I prefer HSQL ) and have much more database flexibility ( functions, including Date Arithmetic ), Triggers, create your own functions, get at your database outside of OpenOffice / LibreOffice Base, and other external utilities.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Problem with date_add function
Posted: Fri Jan 12, 2018 7:00 am
by assa
Thanks for the help.
I understand that I need to use functions from HSQL version 1.8.0.10.