6

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.

Artemination
  • 703
  • 2
  • 10
  • 30

2 Answers2

6

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'
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
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)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Nop, Just work with cast(calldate as date) = '20170901' , but if I put cast(calldate as date) >= '20170901' then it doesn't work. – Artemination Sep 27 '17 at 17:55
  • 1
    Hiding `calldate` inside a function (`CAST`) makes it impossible to use the index for efficiency. – Rick James Sep 27 '17 at 18:08