CREATE PROCEDURE get_scheduledMsg()
BEGIN
DECLARE _dateformat varchar(100);
SET _dateformat = SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE classCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = _dateformat)
AND sectionCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = _dateformat);
END;
SQL execution error #1064. Response from database:
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 'SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE' at line 4
解决方案Firstly you appear to be using a system variable instead of a local variable ... that should be @dateformat
Also, depending on the version of MySQL you don't need to declare local variables - MySQL will declare them via the SET
You also don't need that SELECT in SET _dateformat = SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
Try the following insteadCREATE PROCEDURE get_scheduledMsg()
BEGIN
SET @dateformat = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE classCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat)
AND sectionCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat);
END;
If you are still having issues then try the following insteadDELIMITER
CREATE PROCEDURE get_scheduledMsg()
BEGIN
SET @dateformat = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
SELECT * FROM studentdetails WHERE classCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat)
AND sectionCode IS IN (SELECT class FROM messagedetails WHERE isStatus ='1' AND datecreated = @dateformat);
END
DELIMITER ;
I've now noticed that you are comparing datecreated with @dateformat which implies that that column is a varchar on table messagedetails - it is a very bad idea to store date/datetime values in varchar columns - use the proper datatype