1

I Want to delete some records before a certain date. When i execute the following statements: DELETE FROM 'client_update_history' WHERE DATE(date_history) < '2015/11/01'

i get a error. date_history is a DateTimeField;

user3552264
  • 147
  • 1
  • 2
  • 12
  • `WHERE date_history < '2015-11-01 00:00:00'` Other examples: http://stackoverflow.com/questions/8359457/deleting-records-before-a-certain-date – xQbert Dec 04 '15 at 16:13
  • I tried this but i still get the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''client_update_history' WHERE DATE(date_history) < '2015-11-01 00:00:00'' at line 1 – user3552264 Dec 07 '15 at 15:38

3 Answers3

4

Seems you do not have correct date format. The correct mysql format is yyyy-MM-dd

DELETE FROM client_update_history WHERE DATE(date_history) < '2015-11-01'

Do note that if you are using date_history as an index you are better of doing

DELETE FROM client_update_history WHERE date_history < '2015-11-01'

so that this index can be used.

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
  • I tried this but i still get the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''client_update_history' WHERE DATE(date_history) < '2015-11-01 00:00:00'' at line 1 – user3552264 Dec 07 '15 at 15:38
  • Your not really using my answer... copy and pass my sql, client_update_history need's to be a table, if you have not a default schema set you need to include it in query, date_history needs to be DATETIME column or DATE column, note do not use ' when indicating table if you need to (strange names) use ` – Petter Friberg Dec 07 '15 at 15:55
  • Hi Peter, You're right. Your statement does work. Thanks again. – user3552264 Dec 08 '15 at 07:56
  • BTW, what do you mean by Default schema? – user3552264 Dec 08 '15 at 07:57
  • A mysql can have multiple schema, a schema is like a sub database. When you connect to a database you can provide the default schema you like to use, this means that in query you do not need to specify the schema, if you have not provided the schema, you need to specify in query...es. myschema.client_update_history. Remember to accept an answer so that question can be closed. – Petter Friberg Dec 08 '15 at 14:15
1

You have to include the time as well, using this format: YYYY-MM-DD HH:MM:SS

DELETE FROM 'client_update_history' WHERE DATE(date_history) < '2015/11/01 00:00:00'

Reference from the MySQL website

Naimah Nash
  • 162
  • 2
  • 4
  • I tried this but i still get the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''client_update_history' WHERE DATE(date_history) < '2015/11/01 00:00:00'' at line 1 – user3552264 Dec 07 '15 at 15:38
1

You'd better convert '2015/11/01' to date as well: STR_TO_DATE('2015/11/01', '%Y/%m/%d'). By doing this, you're informing mysql how to parse the date you want to use.

Jodevan
  • 732
  • 1
  • 6
  • 20