Page 1 of 1

[Solved] Problem with date_add function

PostPosted: 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   Expand viewCollapse view
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

PostPosted: 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:

  1. Use the HSQL Function DATEADD instead. The syntax for your Query would be:

    Code: Select all   Expand viewCollapse view
    select
       "p_birthdate",
       DATEADD('month', 3, "p_birthdate"
    from "tbl_person"


  2. 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

    1. From the Menu: Edit -> Run SQL Command directly[ should be checked

    2. 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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.