I am querying a table with where condition in which i want to choose between a particular date range at present my query is like this with a where condition.

WHERE entered_by = '1' AND date_created = '2018-08-23'

I want to use a particular date range from to to how can i do this?


The fiddle shows that candidate.date_created data type is VARCHAR:

`date_created` varchar(250) COLLATE utf8_unicode_ci DEFAULT ''

If so it must be explicity converted to a DATE data type when we use date literals in a compare operator and want to have a date type compare. Like:

WHERE CAST(date_created AS DATE) BETWEEN '$from_date' AND '$to_date'

Using CAST in the first operand forces the server to use DATE data type context. So all other operands will be converted to this datatype before compare. And the result will be one we need.

  • I down-voted this because there are at least three reasons why this is not a good way to handle dates and date ranges. I cover them in my Answer. – Rick James Sep 30 '18 at 17:50

Don't use functions on indexed columns.

Don't put dates in VARCHAR; use DATE, DATETIME, TIMESTAMP, etc. MySQL does not understand tz info, as in 2018-07-16T09:48:30+00:00; deal with it before storing into the date column.

Do test ranges this way:

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 7 DAY

to test for a week. Note that you don't need to worry about leap years, etc. For a single day, do use

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Do not use this (or CAST):

WHERE DATE(date_created) = '2018-08-23'    -- inefficient

because no index can be used.

So, if your query is

WHERE entered_by = '1' AND date_created = '2018-08-23'  -- original

change it to

WHERE entered_by = '1'
  AND date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Note further: This will work regardless of whether date_created is DATE, DATATIME, or even DATETIME(6). And it avoids the clumsy 23:59:59 (which won't be adequate for DATETIME(6)).

Also, have this "composite" index:

INDEX(entered_by, date_created)   -- in this order

A further note: BETWEEN leads to the "midnight bug", wherein you accidentally include an extra midnight. Note that my code has >= and <, not <=.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.