[Solved] Problem with date_add function

Creating tables and queries

[Solved] Problem with date_add function

Postby assa » Thu Jan 11, 2018 7:12 am

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

Re: problem with date_add function

Postby Sliderule » Thu Jan 11, 2018 7:37 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1128
Joined: Thu Nov 29, 2007 9:46 am

Re: problem with date_add function

Postby assa » Thu Jan 11, 2018 11:41 am

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

Re: problem with date_add function

Postby robleyd » Thu Jan 11, 2018 12:02 pm

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
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1502
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem with date_add function

Postby Sliderule » Thu Jan 11, 2018 4:46 pm

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.
User avatar
Sliderule
Volunteer
 
Posts: 1128
Joined: Thu Nov 29, 2007 9:46 am

Re: Problem with date_add function

Postby assa » Fri Jan 12, 2018 7:00 am

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest