How to Use Where clause with Datetime to Update records

Question

Can I know what is wrong with my sql statement below. I gives an error "incorrect datetime value".

UPDATE stocks SET  Notes = '0' where ExpireDate like '%2017-12-28%' and StockCount > 0;

did you try this https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle — Umar Majeed, Dec 28 '17 at 14:56

score 1 · Answer 1 · edited Dec 29 '17 at 09:37

1

Try this:

SET sql_safe_updates = 0;
UPDATE STOCKS
SET NOTES = '0'
WHERE EXPIREDATE = CURDATE()
AND STOCKCOUNT > 0;
SET sql_safe_updates = 1;

edited Dec 29 '17 at 09:37

Hasaan Mubasher

answered Dec 28 '17 at 15:03

S_sauden

It still does not work. Gives the error "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode" – Pathum Dec 29 '17 at 05:06
Actually what I need is to get today's date and if it matches with the expire date of a product, change the status to expire.. please tell me if there is any other way to do this or if there is any mistake in my sql. – Pathum Dec 29 '17 at 05:09

1 Answers1