[Solved] MySQL DATE_ADD with Interval?

[leeand00]

I noticed that this query doesn't work when you're adding query parameters (in parsing mode), is there any way to get this to work?

Code: Select all

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);

On a related note, I saw that there is an open bug for this here.

[rudolfo]

I don't see any parameters in your query. But I guess it makes no difference because even without parameters Base reports an error when "Direct SQL" is not activated.
What do you want to substitute:

Code: Select all

 SELECT DATE_ADD(:startdate, INTERVAL 31 DAY)
or
 SELECT DATE_ADD('2008-01-02', INTERVAL :numdays DAY)

I wouldn't expect the second one to work, because INTERVAL, :numdays and DAY are simply too many tokens to be parsed. "DATE_ADD()" seems to be special for MySQL. PostgreSQL that is usually close to the standard doesn't have this function.

[leeand00]

How do people do queries that return fields that have date math in them when they're using OOo Base?
(I understand that I could probably just do something with OOo macros, but my intent is to eventually (in a larger query) sort them by this computed date)

[Sliderule]

Just as an FYI . . . I am using OpenOffice 3.2.1 with MySQL ( I really prefer HSQL 2.0.1 . . . but . . . also use MySQL at times ).

OpenOffice Base 3.2.1, when using the GUI ( Graphic User Interface ) will accept the date arithmetic functions:

1. ADDDATE
2. SUBDATE

Therefore, in your FIRST post above, the following SQL will work . . . whether Run DIrectly, OR, having the Base Parser go through it:

Code: Select all

SELECT ADDDATE( CAST('2008-01-02' as DATE), 31) as "Add 31 Days"	

For MySQL documentation on the use of the above two functions . . . see the link below:

http://dev.mysql.com/doc/refman/5.0/en/ ... on_adddate

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.

[leeand00]

Well it works in Run Directly (I'm not suprized by that), but when it comes to running it in parser mode, it doesn't work, rather I get:

Code: Select all

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

P.S. I'm using the JDBC driver and OpenOffice 3.2.1 with MySQL 5.1.

(I need to run it in parser mode so I can add the parameters to the query)

Thanks!

[Sliderule]

Please try the following code, BUT, substitute `MyTable` with a real table on your system, and you can change CURRENT_DATE to any table field of type DATE:

Code: Select all

SELECT 
   ADDDATE(CURRENT_DATE, 31) as `Add 31 Days`,
   ADDDATE(CURRENT_DATE, :How_Many_Days) as `Ask Days In Future`
From `MyTable`

Note: I am using MySQL ODBC, but, I do NOT think that should make a difference.

Also, if run with the Parser ( NOT with Run Directly ), you will have to 'format' the value returned above to a date ( it returns a number ).

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[leeand00]

Whoa! What wonderus magic you have worked here Sliderule! Thank you! Thank you! Thank you! That did the trick!

[Sliderule]

Just like to add one additional note . . . but ALSO including the use of the MySQL function Date_Format :

According to MySQL documentation found at:

http://dev.mysql.com/doc/refman/5.0/en/ ... ate-format

You can include a pre-defined DATE format for your answer ( and yes, OpenOffice Parser will accept it )

Not knowing your location on the third rock from the sun . . . whether you might want a date formatted like one of the following ( these are just some samples ):

1. %Y-%m-%d
2. %d-%m-%Y
3. %m-%d-%Y
4. %m/%d-%Y
5. %m/%d/%Y

Therefore, you could include the MySQL function Date_Format in the SQL . . . something like:

Code: Select all

SELECT
   DATE_FORMAT( ADDDATE(CURRENT_DATE, 31), '%Y-%m-%d') as `Add 31 Days`,
   DATE_FORMAT( ADDDATE(CURRENT_DATE, :How_Many_Days), '%m/%d/%Y') as `Ask Days In Future`
From `MyTable`

Just wanted to let you know about including DATE_FORMAT function with the desired format surrounded by single quotes.

Sliderule

[leeand00]

That's was I've been doing...but thanks again! That really did the trick!