PHP MyAdmin syntax error in declaring Date variable

Question

I cannot seem to get a DATE declared as a variable in phpMyAdmin. I always seem to get the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@check_in date = '2019-12-11', @check_out = '2019-12-17';

The syntax of my query has gone from :

Declare @checkIn date = '2019-12-11'
    , @checkOut date = '2019-12-17'

select *
from CURRENT_BOOKINGS
where booked_from not between @checkIn and @checkOut
and booked_to not between @checkIn and @checkOut'

To ...

DELIMITER $$

CREATE PROCEDURE roomAvailable()
BEGIN

DECLARE @check_in DATE;  
SET @check_in = '2019-12-11';
DECLARE @check_out DATE 
SET @check_in = '2019-12-17'

SELECT * FROM CURRENT_BOOKINGS
WHERE booked_from NOT BETWEEN @check_in AND @check_out
AND booked_to NOT BETWEEN @check_in AND @check_out

END $$

as I have gone through multiple iterations trying to find the cause. Any help appreciated as I am stumped

juergen d · Accepted Answer · 2019-12-05 11:02:27Z

1

Don't use @ and all declares have to be at the beginning

DELIMITER $$

CREATE PROCEDURE roomAvailable()
BEGIN

   DECLARE check_in DATE;
   DECLARE check_out DATE;
   SET check_in = '2019-12-11';
   SET check_out = '2019-12-17';

   SELECT * FROM CURRENT_BOOKINGS
   WHERE booked_from NOT BETWEEN check_in AND check_out
   AND booked_to NOT BETWEEN check_in AND check_out;

END $$

See the different types of MySQL variables

answered Dec 5, 2019 at 11:02

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

2 Comments

Murphler Over a year ago

Many thanks - I have now run into another error: #1558 - Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 100128, now running 100408. Please use mysql_upgrade to fix this error So it appears I may be running a slightly outdated version, so a whole new range of issues. Thanks anyway

Isaac Bennetch Over a year ago

When you upgraded your MariaDB from version 10.01 to 10.04, you just missed the step of running the mysql_upgrade script that's included with the distribution. If you run that, it will fix the proc table.

Collectives™ on Stack Overflow

PHP MyAdmin syntax error in declaring Date variable

1 Answer 1

See the different types of MySQL variables

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related