varchar date time comparison issue

Question

I have a legacy table which has a varchar column represent date, format is MM/DD/YYYY (e.g. 01/08/2015). It is not convenient to perform data range selection since it is a varchar (when I use < or > kinds comparison, it goes to varchar/string comparison, which have different results from date comparision).

For example, I want to select only rows which dates are between 01/08/2015 and 01/10/2015. Any smart solution is appreciated, and I cannot change the data type of varchar to date in my existing table.

I am using MySQL Workbench/MySQL.

If you can't change the data type find someone that can. Constantly converting strings to dates and back is not only going to be a performance issue, it is likely to throw up conversion errors too. The sooner you solve the real problem, rather than finding work arounds, the better. — GarethD, Aug 26 '15 at 07:46

score 12 · Accepted Answer · answered Aug 26 '15 at 07:42

12

Varchar dates are evil and they are not real date, the best solution is to use mysql's native date data types.

Since you can't change the datatype you may use str_to_date() function and here how it works

mysql> select str_to_date('01/08/2015','%d/%m/%Y') as d ;
+------------+
| d          |
+------------+
| 2015-08-01 |
+------------+

So the query for select would be

select * from table_name
where
str_to_date(date_column,'%d/%m/%Y')
between
str_to_date('01/08/2015','%d/%m/%Y')
and
str_to_date('01/10/2015','%d/%m/%Y')

answered Aug 26 '15 at 07:42

Abhik Chakraborty

44,654
6
52
63

2

Upvoting simply for the sentence *"Varchar dates are evil"* - Stackoverflow would be nowhere near 10,000,000 questions if people used the appropriate data types! – GarethD Aug 26 '15 at 07:46
@GarethD you are correct a wrong data type could mess up so badly it becomes so difficult to handle later once the data size starts increasing and for dates using varchar datatype will make it more pathetic for large data set where you cant even use the index. – Abhik Chakraborty Aug 26 '15 at 07:49
1

@LinMa good it works now, but make sure to find some time to fix the wrong datatype. I would suggest to create a new column in the table and then transfer all the dates to that new column using `update table_name set new_col = str_to_date(date_column,'%d/%m/%Y')` then drop the old column , rename the new column as your old one and make sure that the application is inserting date in `Y-m-d` format. I bet life will be easy after that :) – Abhik Chakraborty Aug 26 '15 at 07:57

Rakesh Shewale · Answer 2 · 2015-08-26T07:48:59.100

There are many answers which addresses many different way of converting the string to date.

You may choose whichever is perfect for your need

SELECT * FROM `your_table` WHERE DATE_FORMAT(my_column_with_the_string_date, "%Y-%m-%d") <= '2011-09-30'

DATE_FORMAT can be used to convert your date string to any format: I will use the NOW() function instead of string to list different formats that are supported

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

The output of the above is:

Nov 04 2014 11:45 PM
11-04-2014
04 Nov 14
04 Nov 2014 11:45:34:243

You can modify your query accordingly

score 1 · Answer 3 · answered Aug 26 '15 at 07:42

1

You can cast your dates as strings using STR_TO_DATE:

STR_TO_DATE(yourdatefield, '%m/%d/%Y')

answered Aug 26 '15 at 07:42

A Hocevar

726
3
17

score 1 · Answer 4 · answered Aug 26 '15 at 07:42

1

SELECT STR_TO_DATE(got_fired_at, '%m/%d/%Y') BETWEEN ? AND ? FROM firings;

(field/table names guaranteed to have been chosen randomly)

answered Aug 26 '15 at 07:42

Amadan

191,408
23
240
301

score 1 · Answer 5 · answered Aug 26 '15 at 07:42

1

Use MySQL's STR_TO_DATE function to parse the date strings to date objects then do the comparison.

answered Aug 26 '15 at 07:42

marekful

14,986
6
37
59

varchar date time comparison issue

5 Answers5

Linked