Search code examples
c#mysqlsqlwhere-clausesql-delete

There seems to be an error with mySQL database that i can't fix. ERROR Code 1292:

i get an error when putting in the code

delete from littering_event_alarm where Event < date_sub(now(), interval 30 DAY);

This is the full error Code: 1292. Truncated incorrect datetime value: '21/07/2020 16:05:35'

any help is appreciated

Solution

  • The error message indicates that event is of a string datatype, not of a date-like datatype (datetime, timestamp).

    Using the wrong datatype is the root of many evils (eg: it puts at risk the integritu of your data and hurts efficiency), and I would highly recommend fixing your data model and store dates as dates.

    In the meantime: if event consistently uses format dd/mm/yyyy hh:mi:ss, then you can use str_to_date() to turn it to a datetime:

    delete from littering_event_alarm  
where str_to_date(event, '%d/%m/%Y %h:%i:%s') > now() - interval 30 day