mysql - 在mysql中使用变量进行查询

Question

谁能帮我解决这个问题？

服务器版本:5.5.34-0ubuntu0.12.04.1

mysql> SET @MY_CURRENT_DATE = CAST( CONCAT( CURDATE(), ' 00:00:00' ) AS DATETIME );
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MY_WEEKDAY = WEEKDAY( NOW() );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DATE_SUB( @MY_CURRENT_DATE, INTERVAL @MY_WEEKDAY + 14 DAY ), DATE_SUB( @MY_WEEKDAY + 8 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

Answer 1

您错误地使用了Date_sub 函数。它需要一个 date 值作为第一个参数，当您输入一个 int(工作日)值并且输入参数数量不足时。因此是错误。

-- this is wrong
DATE_SUB( @MY_WEEKDAY + 8 DAY ) 

-- this is right
DATE_SUB( @MY_CURRENT_DATE, INTERVAL @MY_WEEKDAY + 14 DAY )

-- this is corrected to be right
DATE_SUB( @MY_CURRENT_DATE, INTERVAL @MY_WEEKDAY + 8 DAY ) 

语法:

• > DATE_ADD(date,INTERVAL expr unit) .
• > DATE_SUB(date,INTERVAL expr unit) .