CODEWITHSUNDEEP
mysql
Ricardo Prieto
Ricardo Prieto

Reputation: 447

Mysql selecting unique values in date type varchar

I have a column where the dates are type varchar. For example: 

15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018
10-11-2018
12-11-2018

when I consult with the following query 

SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '9-11-2018'.

I have the right result. 

15-10-2018
16-10-2018
19-10-2018
23-10-2018
29-10-2018
8-11-2018
9-11-2018

but if the query is: 

SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '10-11-2018'.

or

SELECT DISTINCT date FROM `test` WHERE date BETWEEN '15-10-2018' and '12-11-2018'.

The answer I get is empty.

I think it's only validating the days in the sql.

I need to get the right dates.

Upvotes: 0

Views: 294

Answers (2)

CarlosH.
CarlosH.

Reputation: 736

I think the problem is the fact that the column is varchar, so it's comparing characters instead of a range of dates. I will recommend convert the column to date type and try again.

Alternative if you cannot change the type of the column you could cast it to date format like this:

SELECT DISTINCT `date` FROM `test` WHERE STR_TO_DATE(`date`,'%d-%m-%Y') BETWEEN '2018-10-15' AND '2018-11-10';

I tested with your data and it works. Of course this could put some extra effort on the database and will not use indexes.

Upvotes: 1

Simon R
Simon R

Reputation: 3772

You need to set the datatype to date and update your dates to be using date for a more reliable result. Once done you should be using the database format for the dates in your WHERE clause.

Try

SELECT DISTINCT date FROMtestWHERE date BETWEEN '2018-10-15' and '2018-11-10'

Upvotes: 1

Related Questions