[Solved] Problem with date_add function

Creating tables and queries
Post Reply
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

[Solved] Problem with date_add function

Post 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!
Last edited by assa on Fri Jan 12, 2018 7:01 am, edited 1 time in total.
OpenOffice 4.1.4 and Operating System Windows 8.1
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: problem with date_add function

Post 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

    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.
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: problem with date_add function

Post by assa »

Thanks for the reply.
But my problem still continues. I tried both the solutions but no result.
OpenOffice 4.1.4 and Operating System Windows 8.1
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: problem with date_add function

Post 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?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Problem with date_add function

Post 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.
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: Problem with date_add function

Post by assa »

Thanks for the help.
I understand that I need to use functions from HSQL version 1.8.0.10.
OpenOffice 4.1.4 and Operating System Windows 8.1
Post Reply