CODEWITHSUNDEEP
mysqlsql
BuzzLightYear
BuzzLightYear

Reputation: 49

Sql condition to return specific date range

I am trying to get a date range using the following SQL condition in MySQL.

and a.timestamp >= '2016-03-29'
and a.timestamp <= '2016-03-30'

This returns 0 rows but when I try a longer range like

and a.timestamp >= '2016-03-20' and a.timestamp <= '2016-03-31'

It returns the rows I want which is shown below in the timestamp column enter image description here

Please what condition for the 2016-03-29 and 2016-03-30 would return the row in the image. Thanks

Upvotes: 1

Views: 82

Answers (3)

levelonehuman
levelonehuman

Reputation: 1505

You can also CONVERT the date, stripping the time:

and CONVERT(a.timestamp, date) >= '2016-03-29'
and CONVERT(a.timestamp, date) <= '2016-03-31'

The reason your query is not returning certain rows is because 2016-03-31 00:00:01 is greater than 2016-03-31

You can also make use of the BETWEEN clause:

AND CONVERT(a.timestamp, date) BETWEEN '2016-03-29' AND '2016-03-31'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Presumably, you have no values on 2016-03-29. So, you only want to consider the date portion. One method is to use date():

date(a.timestamp) >= '2016-03-29' and date(a.timestamp) <= '2016-03-30'

However, that is a bad habit, because the use of the function precludes the use of an index (if available). Instead, you probably intend:

a.timestamp >= '2016-03-29' and a.timestamp < '2016-03-31'

Note the change of <= to < for the second condition. This gets everything before that date, but not at midnight when the day begins.

Upvotes: 3

NineBerry
NineBerry

Reputation: 28539

Any time after midnight 2016-03-30 is greater than 2016-03-30, so you need to check like this to get values like "2016-03-30 15:55"

and a.timestamp >= '2016-03-29'
and a.timestamp < '2016-03-31'

This will return all datetime values on March 29th and March 30th.

Upvotes: 3

Related Questions