MySQL Forums
Forum List  »  Newbie

Equivalent date interval query does not compile
Posted by: Dmitry Katsubo
Date: September 05, 2011 06:46AM

Dear MySQL community,

Can somebody explain, why the following query compiles:
mysql> prepare st2 from "select count(*) from document where cast(image_indexing_date as date) >= current_date - interval ? day";
Statement prepared
and the following equivalent query does not:
mysql> prepare st2 from "select count(*) from document where current_date - cast(image_indexing_date as date) <= interval ? day";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
as well as the following fails:
mysql> prepare st2 from "select count(*) from document where current_date - cast(image_indexing_date as date) <= ? day";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'day' at line 1

SQL'92 standard states, that the subtraction of two dates is date interval. HSQL / DB2 support similar syntax, but MySQL does not.

Any feedback is welcomed.

Additional info:
* image_indexing_date has type datetime
* MySQL v5.1.36

Options: ReplyQuote


Subject
Written By
Posted
Equivalent date interval query does not compile
September 05, 2011 06:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.