0

Hi so I am trying to delete rows in mysql where the dates are equal to a start and end date. The problem is when I run this query:

DELETE FROM taddmapp.sensorsummaryall WHERE startDate = '2017-07-14T03:01:42-04:00' AND endDate = '2017-07-14T19:10:36-04:00';

i get an error saying "Error Code: 1292. Incorrect datetime value: '2017-07-14T03:01:42-04:00' for column 'startDate' at row 1".

The only thing is when i run this select query: 

SELECT * FROM taddmapp.sensorsummaryall WHERE startDate = '2017-07-14T03:01:42-04:00' AND endDate = '2017-07-14T19:10:36-04:00';

with the same dates for start and end it returns me all the rows that have start and end date equal. Can someone explain why the SELECT works but the DELETE gives me an error? Thank you in advance for your time and effort.

Justin Eckhart
  • 45
  • 7
  • What am I missing? _'If I run the query with the same dates for start and end, then it shows me the rows that have the same start and end dates.'_ Of course? – underscore_d Jul 25 '17 at 14:46
  • 1
    Possible duplicate of [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) – underscore_d Jul 25 '17 at 14:47
  • and to show that `between` works for `delete` too: [How to Delete range of records at once - mysql](https://stackoverflow.com/questions/7972435/how-to-delete-range-of-records-at-once-mysql?lq=1) – underscore_d Jul 25 '17 at 14:47
  • 1
    @underscore_d i meant wy the delete isnt working? The select statement is working fine. – Justin Eckhart Jul 25 '17 at 14:48
  • I doubt it. If you want to delete rows between those start and end dates, then an `and` clause is not the way to capture those rows. – underscore_d Jul 25 '17 at 14:49
  • @underscore_d each row has a startdate and enddate that they are equal to. sorry i didnt mean to say range. – Justin Eckhart Jul 25 '17 at 14:51
  • Type of `startDate` in table? – Nana Partykar Jul 25 '17 at 14:52
  • Did you google the **error you get**? Your title does NOT reflect the error, so wondering what you found when you did your own research. – random_user_name Jul 25 '17 at 14:53
  • @NanaPartykar the type is datetime – Justin Eckhart Jul 25 '17 at 14:54
  • @cale_b yes i googled it but they usually came up with php. im having trouble understanding why the dates are recognized when doing select but dont work when doing the delete? – Justin Eckhart Jul 25 '17 at 14:56
  • 2
    MySQL does not properly support ISO 8601 format for dates: https://stackoverflow.com/questions/11053941/mysql-insert-to-datetime-is-it-safe-to-use-iso8601-format – CodeCaster Jul 25 '17 at 14:58

0 Answers0