CODEWITHSUNDEEP
javamysqlsqldatabasemysql-workbench
ed tru
ed tru

Reputation: 101

MYSql Stored Procedure, DateTime value not working

DELIMITER // 
CREATE PROCEDURE GetWinners(IN Number INTEGER , IN Table_Name VarChar(7)) 
BEGIN 
 SET @t1 = CONCAT('Select * FROM ', Table_Name, ' Where number = ', Number, ' && active = true && dateAndTime < 2015-02-10');
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
END // 
DELIMITER ;

dateAndTime is a column where I store DateTime values. I can't understand why with && dateAndTime < 2015-02-10', the query returns no results.

If I remove && dateAndTime < 2015-02-10', then some results are returned. This happens even though I have DateTime value lower than 2015-02-10 in the columns.

Upvotes: 1

Views: 1109

Answers (2)

Olla Ashour
Olla Ashour

Reputation: 301

This most probably due to your date format, the time is added to the date but due to your preferences in mysql, it only shows the date without time. Use trim or trunc to remove the time.

TRUNC(dateAndTime) < TO_DATE('2015-02-10', 'YYYY-MM-DD')

Upvotes: 1

theDarse
theDarse

Reputation: 737

Surround your DateTime in single quotes

SET @t1 = CONCAT('Select * FROM ', Table_Name, ' Where number = ', Number, ' && active = true && dateAndTime < '2015-02-10'');

Upvotes: 0

Related Questions