Difference in SELECT WHERE IN with Datetime Fields

[Jim Min]

Anyone can explain the following?

Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

Thanks,

[BDR]

According to the manual, the IN search is performed using a binary
comparison, so it's possible I guess that the SalesDate column isn't
giving the correct output for the comp operation.

This does work though:
SELECT * FROM testdate
WHERE date_format(`SalesDate`,'%Y-%m-%d')
IN('2003-08-27', '2003-07-27');
Jim Min wrote:

Anyone can explain the following?

Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

Thanks,

[BDR]

According to the manual, the IN search is performed using a binary
comparison, so it's possible I guess that the SalesDate column isn't
giving the correct output for the comp operation.

This does work though:
SELECT * FROM testdate
WHERE date_format(`SalesDate`,'%Y-%m-%d')
IN('2003-08-27', '2003-07-27');
Jim Min wrote:

Anyone can explain the following?

Table definition:

CREATE TABLE testdate
(
SalesDate DATETIME,
SalesTotal DOUBLE,
PRIMARY KEY (SalesDate)
)

If I use the following SQL statement I get results:

SELECT * FROM testdate
WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27';

But I get an empty set when I use this:

SELECT * FROM testdate
WHERE SalesDate IN ('2003-08-27', '2003-07-27');

This happens on both 4.0.15 and 3.23.57.
But the above statements work the same under Microsoft SQL Server.

Why is MySQL interpreting WHERE IN and WHERE = OR = differently?

Thanks,