0

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

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Murphler
  • 83
  • 1
  • 6

1 Answers1

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

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 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 – Murphler Dec 05 '19 at 11:25
  • 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. – Isaac Bennetch Dec 10 '19 at 18:48