CODEWITHSUNDEEP
mysqldate
snaggs
snaggs

Reputation: 5713

What the differnce between mysql date defined as String and wrapped by "date()"?

I have pretty basic query:

SELECT count(*)  FROM myTable WHERE report_date='2013-12-01';

Result:

 100

On other hand followed example gives me other result (with offset of 1)

SELECT count(*)  FROM myTable WHERE date(report_date) = '2013-12-01';

Result:

 101 // Hmmm

Please help,

Thanks,

[EDIT]

report_date has a Type of datetime

Upvotes: 1

Views: 54

Answers (3)

peterm
peterm

Reputation: 92805

Apart from the why, which is already explained by others, the best possible and most index-friendly way to query for date ranges on a column with the time component is

SELECT COUNT(*)  
  FROM myTable 
  WHERE report_date >= :start_date
    AND report_date <  :end_date + INTERVAL 1 DAY;

Here is SQLFiddle demo

Don't use any functions (e.g. DATE()) on the left side of a condition in a WHERE clause like in your example

WHERE date(report_date) = '2013-12-01'

By doing so you prevent MySQL from using any index(indices) you may have created on this column effectively causing a full scan.

Do all necessary transformation on the right side applying them to the constant (parameter) values.

Recommended reading:

Upvotes: 5

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

Maybe one of your record has timestamp part other that 00:00:00 in the report_date column. So it is not matched when match is performed with 2013-12-01. Whereas when you explicitly use DATE() then only date part is matched and timestamp is ignored

Working Demo: http://sqlfiddle.com/#!2/ae63a/4

Upvotes: 1

CompuChip
CompuChip

Reputation: 9232

If report_date is a DATETIME or a TIMESTAMP it contains a time part (which defaults to 0:00). If you have one record for which the value is '2013-12-01 3:42:00' then the comparison report_date = '2013-12-01' will fail because the string on the right hand side converts to '2013-12-01 0:00:00' which is not '2013-12-01 3:42:00'.

If you apply DATE, then you will get DATE('2013-12-01 3:42:00') = '2013-12-01' and the record will be included in the resultset.

Upvotes: 3

Related Questions