Hi there,
Tried to use date_format(sysdate(), ‘%y-%m-%d’) but it got me an error message. When I used sysdate() then it’s fine in the trigger exercise. Why is this? Anything wrong or I need to change setting for my Workbench?
DELIMITER $$
CREATE TRIGGER check_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.hire_date > SYSDATE() THEN
SET NEW.hire_date = sysdate();
END IF;
END$$
DELIMITER ;
#####
official solution is as follows:
CREATE TRIGGER trig_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.from_date > date_format(sysdate(), ‘%Y-%m-%d’) THEN
SET NEW.from_date = date_format(sysdate(), ‘%Y-%m-%d’);
END IF;
END $$
DELIMITER ;
Hi Yunfeng!
Thanks for reaching out.
There’s no need to change the settings at this stage at least.
Can you please support your question with the entire error message you’ve obtained (either the text or as a screenshot)?
In this way, we will be able to assist you better. Thank you.
Looking forward to your reply.
Best,
Martin
This is the error message:
Error Code: 1292. Truncated incorrect date value: ‘2020-May-18th’
The code I used was %Y-%M-%D… I figure I cannot do capital letters for m and d, but for Y it’s ok?
IF NEW.hire_date > date_format(SYSDATE(), ‘%Y-%M-%D’) THEN
SET NEW.hire_date = date_format(sysdate(), ‘%Y-%M-%D’);
END IF;
I inserted this below:
INSERT INTO employees
VALUES (‘999996’, ‘1990-03-01’, ‘JOHN’, ‘SMITH’, ‘M’, ‘2021-01-01’);
Hi Yunfeng!
Thank you very much for your reply.
Yes, whether you use capital or small letter matters. You need to use small letters in this example.
%y-%m-%d
Regarding the error message – have you executed the queries you’ve provided in the given order? The error message is actually suggesting that you had probably used the INSERT statement to insert a different record:
INSERT INTO employees
VALUES (‘999996’, ‘1990-03-01’, ‘JOHN’, ‘SMITH’, ‘M’, ‘2020-May-8th’);
Instead, we must use numbers to express the dates – 2020-05-08.
Hope this helps.
Best,
Martin
I didn’t insert a record with “2020-May_8th” for sure. And I am quite confused when I saw this date format myself.
Hi Yunfeng!
Thank you very much for your reply.
I am sorry the previous suggestion did not help. In that case, can you please post the entire code you’ve executed, as well as a screenshot containing the error message that you get (or you can also paste the error message, if you prefer)? I would like to execute the query on my end to see what we can do.
Thank you!
Looking forward to your reply.
Best,
Martin