make query with variable in mysql

Question

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

Ravinder Reddy · Accepted Answer · 2014-01-30 19:39:48Z

3

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:

answered Jan 30, 2014 at 19:39

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Over a year ago

thank you for response, working right now:

SELECT DATE_SUB( @MY_CURRENT_DATE, INTERVAL @MY_WEEKDAY + 14 DAY ), DATE_SUB( @MY_CURRENT_DATE, INTERVAL @MY_WEEKDAY + 8 DAY );

Hituptony · Accepted Answer · 2014-01-30 19:03:48Z

1

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

edited Jan 30, 2014 at 19:03

answered Jan 30, 2014 at 18:58

Hituptony

2,8703 gold badges26 silver badges44 bronze badges

1 Comment

Robert Over a year ago

Thank you for your help, but unfortunately this still fail

SELECT DATE_ADD( @MY_CURRENT_DATE, INTERVAL ( @MY_WEEKDAY + 14 DAY ) ), DATE_ADD( @MY_WEEKDAY, INTERVAL + 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 ) ), DATE_ADD( @MY_WEEKDAY, INTERVAL + 8 DAY )' at line 1

Collectives™ on Stack Overflow

make query with variable in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related