MySQL DISTINCT date only returning 1 value for particular table. Same query works on other tables-CodePudding

I have 2 tables with the exact same structure:

date (date)
title (varchar 275)

Both tables have rows for dates including 2021-01-01 to 2021-12-31

If I run this query on table1:

SELECT DISTINCT date FROM table1 WHERE date IN ('2021-01-02', '2021-01-04')

it returns the expected values of:

2021-01-02
2021-01-04

However, if I run it on table2 I only get 1 result which is the earliest date in the range. For example if I run the same query I only get 2021-01-02. If I change the query to:

SELECT DISTINCT date FROM table2 WHERE date IN ('2021-01-04', '2021-01-06')

I only get 2021-04-04. If I change the dates to '2021-01-06' and '2021-01-07' I only get 2021-01-06, etc, etc.

If I run the exact same query on table1, it returns all expected values. I assume it must be a difference in the tables but they have the exact same fields, field types, db collation, etc. It behaves as if there is a LIMIT 1.

EDIT @Joseph: If I remove the DISTINCT and run the following query:

SELECT date FROM table2 WHERE date IN ('2021-01-04', '2021-01-06')

it returns the correct dates(duplicated):

2021-01-04
2021-01-04
2021-01-04
...
2021-01-06
2021-01-06

Adding the DISTINCT back in results in(only for table2):

2021-01-04

CodePudding user response：

For some reason, you need to convert your date column to the actual date so you could do that like this

SELECT DISTINCT date FROM table2 WHERE Date(date) IN ('2021-01-04', '2021-01-06')

CodePudding user response：

if your date column is string(varchar) data type.

Try this

SELECT district date 
FROM table1 
WHERE 
   STR_TO_DATE(date, '%m/%d/%Y') BETWEEN CAST('2021-01-04' AS DATE) AND CAST('2021-01-06' AS DATE)

or if your date column is date data type.

Try this

SELECT district date 
FROM table1 
WHERE 
   date BETWEEN CAST('2021-01-04' AS DATE) AND CAST('2021-01-06' AS DATE)