CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 661

make query with variable in mysql

Could anyone help me with this?

Server version: 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

Upvotes: 0

Views: 347

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

You have wrongly used Date_sub function. It needs a date value as first parameter, where as you input an int (weekday) value and an insufficient number of input parameters. And hence is the error.

-- 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 )

Syntax:

Upvotes: 3

Hituptony
Hituptony

Reputation: 2860

Well, you're getting a syntax error on the DAY area...so it should have to do with something in that general vicinity.

Looking at what you're trying to do, and what function you're using. I can say:

Its cause you're using DATE_SUB and trying to add...why not use DATE_ADD plus you're missing the INTERVAL part of your second aggregate. Forgetting words,and not using the function appropriately will result in MySQL not knowing what you're trying to do. The first agg uses appropriate syntax, the second one doesn't. But it should look roughly like:

mysql> SELECT DATE_ADD( @MY_CURRENT_DATE, INTERVAL (@MY_WEEKDAY + 14 DAY )), ____________;

It would more than likely be something like:

DATE_SUB( @MY_WEEKDAY, INTERVAL 8 DAY)

but again, not sure what you're trying here...

Upvotes: 1

Related Questions