Apache OpenOffice Community Forum

I want to use date_add function for which i have written a query as follows:
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!

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

   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.

Thanks for the reply.
But my problem still continues. I tried both the solutions but no result.

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?

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.

Thanks for the help.
I understand that I need to use functions from HSQL version 1.8.0.10.