mysql stored procedure syntax errors with dates

Question

Hi I am having fun trying to get a stored procedure to parse correctly in MySQL.

My issue is with dates. I am trying to get the store procedure to create a start date that is the beginning of the current month e.g. 2009-07-01. Using this date I then use the DATA_ADD() function to add a month so that it reads 2009-08-01.

However my problem is that when I try and run the procdure to create it I get the following error:

Script line: 7  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 'DECLARE 
cur_month INT;
SET cur_month = (SELECT MONTH(CURRENT_DATE()));

The code for the store procedure looks like this:

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //

CREATE PROCEDURE sp_test()
BEGIN

  /*we work out the start and end dates*/
  DECLARE cur_year INT;
  SET cur_year = (SELECT YEAR(CURRENT_DATE()));

  DECLARE cur_month INT;
  SET cur_month = (SELECT MONTH(CURRENT_DATE()));


  DECLARE temp_date VARCHAR(10);
  SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));

  DECLARE start_date DATE;
  SET start_date = (SELECT CAST(temp_date AS DATE)));

  DECLARE end_date DATE;
  SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));


  INSERT INTO my_table (startdate, enddate)VALUES(start_date, end_date);

 END; //

DELIMITER ;

I've run the queries independantly and they all return correct values and work, it only starts to fail with syntax errors when I add them into a stored procedure.

What am I missing here that is causing all my head aches?

Thanks...

Nick Fortescue · Accepted Answer · 2009-07-03 14:02:44Z

2

You need to DECLARE all variables at the start of the BEGIN - END block. You also have a mismatched parentheses (extra close) in your CAST line. The following should work:

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //

CREATE PROCEDURE sp_test()
BEGIN

  /*we work out the start and end dates*/
  DECLARE cur_year INT;
  DECLARE cur_month INT;
  DECLARE temp_date VARCHAR(10);
  DECLARE start_date DATE;
  DECLARE end_date DATE;

  SET cur_year = (SELECT YEAR(CURRENT_DATE()));

  SET cur_month = (SELECT MONTH(CURRENT_DATE()));

  SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));
  SET start_date = (SELECT CAST(temp_date AS DATE));

  SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));
  INSERT INTO my_table (startdate, enddate)VALUES(start_date, end_date);


 END; //

DELIMITER ;

answered Jul 3, 2009 at 14:02

Nick Fortescue

44.3k27 gold badges109 silver badges137 bronze badges

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

1 Comment

James Over a year ago

I done this first! Although I have noticed I left the extra ) in the cast line!

Alexander Azarov · Accepted Answer · 2009-07-03 13:59:52Z

1

I've tried your example, it works in my MySQL 5.0.32 :

delimiter //
CREATE PROCEDURE sp_test()
BEGIN
  DECLARE cur_year INT;
  SET cur_year = (SELECT YEAR(CURRENT_DATE()));
  INSERT INTO tt (y) VALUES (cur_year);
END; //
delimiter ;

CREATE TABLE tt (y INT);
CALL sp_test();
Query OK, 1 row affected (0.00 sec)

You can try rewrite SET as

SELECT YEAR(CURRENT_DATE()) INTO cur_year;

Will it work? What's your MySQL version?..

answered Jul 3, 2009 at 13:59

Alexander Azarov

13.3k2 gold badges53 silver badges55 bronze badges

1 Comment

Grant Collins Over a year ago

The version of MySQL I am using is 5.0.45

Alexander Kjäll · Accepted Answer · 2009-07-03 14:06:09Z

You have to declare all variables before you start to assign values to them, the following code seems to work for me:

`DELIMITER $$

DROP PROCEDURE IF EXISTS test.sp_test$$ CREATE PROCEDURE test.sp_test () BEGIN

/we work out the start and end dates/ DECLARE cur_year INT; DECLARE cur_month INT; DECLARE temp_date VARCHAR(10); DECLARE start_date DATE; DECLARE end_date DATE;

SET cur_year = (SELECT YEAR(CURRENT_DATE()));

SET cur_month = (SELECT MONTH(CURRENT_DATE()));

SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));

SET start_date = (SELECT CAST(temp_date AS DATE));

SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));

INSERT INTO alex.my_table (startdate, enddate)VALUES(start_date, end_date);

END$$

DELIMITER ;`

Collectives™ on Stack Overflow

mysql stored procedure syntax errors with dates

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related