Rare Incident when setting dates range in where of a query of MySql?

Question

I'm programming Classic ASP against a MySql Database connecting with ADODB and MySQL ODBC 5.3 ANSI Driver, but I have some problems when setting the date in the where of a simple MySql query, when my query is:

Select * from cdr where date(calldate)='20170901'

The query retrieve data in the asp page, thats ok, but when the query is

Select * from cdr where date(calldate) between '20170801' and '20170828'

When I print the query and then I copy from html and paste into Mysql Workbench, then it retrieve data, but when in the asp page itself does not retrieve a any data.

Any Ideas? I think maybe is something with de ODBC Driver.

Answer 1

If you want use a not mysql default date format you must convert properly

Select * from cdr where date(calldate)=str_to_date('20170901','%Y%m%d')

otherwise use the mysql default format

Select * from cdr where date(calldate)='2017-09-01'

Answer 2

I would not trust this format: '20170801'

Since calldate is DATETIME, this would be faster:

    WHERE calldate >= '2017-08-01'
      AND calldate  < '2017-08-01' + INTERVAL 28 DAY`.

It would require INDEX(calldate)