MySQL Delete from ranged dates

Question

Hello I'm trying to delete all data ranging from 04/15/2014 to 03/16/2016. My problem is that the data type for the dates is varchar. The format is m/d/Y. My current sql code is here

DELETE FROM ending_inventory WHERE STR_TO_DATE(dater, '%m/%d/%Y')  BETWEEN '04/15/2014' AND '03/16/2016'

and it's not doing anything.

Is there a way to do this without changing the data type to DATE? Because I already have tons of data..

Try using DELETE FROM ending_inventory WHERE STR_TO_DATE(dater, '%Y/%m/%d') BETWEEN '2014/04/15' AND '2016/03/16' — Ed de Almeida, Mar 15 '16 at 12:48

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

You are comparing a date to strings. You need to generate dates from the two strings you're trying to compare between. See Jon Skeets answer here: mysql date comparison with date_format

edited May 23 '17 at 11:50

Community

1
1

answered Mar 15 '16 at 12:48

Vipin Jain · Accepted Answer · 2016-03-16T04:51:32.233

1

you can try this. STR_TO_DATE FUNCTION change character into date and its default format is 'YYYY-mm-dd'

 DELETE FROM ending_inventory
 WHERE STR_TO_DATE(dater, '%m/%d/%Y') 
 BETWEEN '2014-04-15' AND '2016-03-16';

DATE FUNCTIONS Hope this will work for you.

edited Mar 16 '16 at 04:51

answered Mar 15 '16 at 12:49

Vipin Jain

3,686
16
35

The dates in the dater field is formatted as m/d/Y and I tried your code, it didn't work. – wobsoriano Mar 15 '16 at 12:50
can you use str_to_date with '2014-04-15' AND '2016-03-16' both dates – Vipin Jain Mar 15 '16 at 12:52
I don't get what you are trying to say with that both dates? – wobsoriano Mar 15 '16 at 12:55
dater is in varchar and dates there are formatted like m/d/Y – wobsoriano Mar 15 '16 at 13:00

score 1 · Answer 3 · answered Mar 15 '16 at 12:51

1

Try this to parse your date:

select date_format(str_to_date('12/31/2011', '%m/%d/%Y'), '%Y%m');

answered Mar 15 '16 at 12:51

Filipe Ramires

79
4

I don't get your answer – wobsoriano Mar 15 '16 at 13:01

MySQL Delete from ranged dates

3 Answers3