1

I want to truncate some records from a table with date time clause.

This link have a solution, but it's not working for me.

TRUNCATE TABLE 'meter_to_sim_mapping' WHERE 'meter_to_sim_mapping'.'mapped_at' <=

In above where clause I want to add the date time value. The value is 2018-04-02 16:03:52. The query should delete all the records prior to this date.

How can I truncate the table with date-time?

Any help would be highly appreciated.

Muhammad Omer Aslam
  • 22,976
  • 9
  • 42
  • 68
Moeez
  • 494
  • 9
  • 55
  • 147

2 Answers2

1

You need to use DELETE

DELETE FROM TABLE_NAME
WHERE DATE_COLUMN < NOW() - INTERVAL N DAY

or 

DELETE FROM TABLE_NAME
WHERE CAST(DATE_COLUMN AS DATE) < STR_TO_DATE('1-01-2012', '%d-%m-%Y')  - INTERVAL N DAY

In place of NOW() You can use your datetime value

STR_TO_DATE('12-01-2014 00:00:00','%m-%d-%Y %H:%i:%s')

Demo

http://sqlfiddle.com/#!9/4607a6/1

Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27
  • I don't want to use `NOW()`. I want to give a date and then subtract the days from it – Moeez Apr 25 '18 at 10:51
  • here's my query `DELETE FROM `meter_to_sim_mapping` WHERE CAST(`meter_to_sim_mapping`.`mapped_at` AS DATE) < STR_TO_DATE('2018-04-02', '%d-%m-%Y') - INTERVAL 2 DAY` – Moeez Apr 25 '18 at 10:58
  • and it's giving me `0 Rows affected` `Warning Code : 1411 Incorrect datetime value: '2018-04-02' for function str_to_date` – Moeez Apr 25 '18 at 10:58
  • again 0 rows affected – Moeez Apr 25 '18 at 11:02
1

Below example may work for you:

DELETE FROM TABLE_NAME
WHERE DATE_COLUMN = DATE_SUB("2018-04-02 16:03:52", INTERVAL 10 DAY);

You can change it accordingly as per your requirement.

Reference: MySQL DATE_SUB() Function

Santosh Jadi
  • 1,479
  • 6
  • 29
  • 55