CODEWITHSUNDEEP
mysqldatetimetriggers
user2540748
user2540748

Reputation:

Error comparing dates using triggers

I'm trying to run the following query:

[SQL]INSERT INTO `Games` VALUES ('18', '21', '101', '98', '2013-10-30', '1190', '2013');
[Err] 1644 - date error

I have a trigger that is:

if (New.date < 1900-01-01 or New.date > 2015-01-01) then 
signal sqlstate '45000' set message_text = "date error"; 
End if;

where games.date is obviously the fifth field.

I'm not really sure why im getting this error as it appears it should work fine.

Upvotes: 0

Views: 153

Answers (1)

Mureinik
Mureinik

Reputation: 311883

1900-01-01 isn't a date - it's three integers separated by minus signs, and will evaluate to 1982. At the very least, you'd want to surround it with single quotes to make it a string literal, but I wouldn't recommend that either - you should always explicitly convert your strings to dates with the appropriate format:

if (New.date < STR_TO_DATE('1900-01-01', '%Y-%m-%d') or 
    New.date > STR_TO_DATE('2015-01-01', '%Y-%m-%d')) then 
    signal sqlstate '45000' set message_text = "date error"; 
End if;

Upvotes: 1

Related Questions