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