CODEWITHSUNDEEP
mysqldate
Neil Nand
Neil Nand

Reputation: 559

MySQL trigger start date is before end date

My server runs MySQL 5.5.40 & I currently have a trigger which first checks that the two date columns (discount_start & discount_end) are either both NULL or both have a date then checks if discount_start is greater than discount_end, the idea being to make sure that the start date isn't after the end date.

The first bit works but the second, checking that the start date isn't after the end date doesn't seem to. I've done a bit of Googleing & it appears that the > operator is allowed here?

The datetime I'm using is 2015-12-10 04:16:34 for discount_start & 2015-12-08 04:16:38 for disocunt_end, the trigger I've written is below.

# Both discount_start & discount_end must either be entered or NULL, one cannot be NULL while the other is entered
IF NEW.discount_start IS NULL AND NEW.discount_end IS NOT NULL THEN 
  signal sqlstate '45000' set message_text = 'discount_end has value but discount_start NULL, both need to have a value or be NULL';
ELSEIF NEW.discount_start IS NOT NULL AND NEW.discount_end IS NULL THEN 
  signal sqlstate '45000' set message_text = 'discount_start has value but discount_end NULL, both need to have a value or be NULL';
# discount_start date must be before discount_end
ELSEIF NEW.discount_start > NEW.discount_end THEN
  signal sqlstate '45000' set message_text = 'discount_start must be before discount_end';
ELSE
  SET new.created = NOW();
END IF

Upvotes: 0

Views: 318

Answers (1)

Darwin von Corax
Darwin von Corax

Reputation: 5256

Try

... ELSEIF DATEDIFF(NEW.discount_end, NEW.discount_start) < 0 THEN ...

Relational operators (<, > &c.) are allowed in trigger code, so I assume the answer you googled is referring specifically to comparing DATE types.

Upvotes: 1

Related Questions