SQL "between" not inclusive

Question

I have a query like this:

SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01' 

But this does not give any results, even if there is evidence of the first.

created_at looks like 2013-05-01 22:25:19 , I suspect that this is due to time? How can this be solved?

It works fine if I do larger date ranges, but it should (inclusively) work with the same date too.

Answer 1

It is on. You are comparing dates and dates. The second date is interpreted as midnight when the day begins.

One way to fix this:

 SELECT * FROM Cases WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01' 

Another way to fix this with explicit binary comparison

 SELECT * FROM Cases WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02' 

Aaron Bertrand has a long date blog post ( here ) where he discusses this and other date issues.

Answer 2

The second date reference in the BETWEEN syntax was supposed to be magically considered the “end of the day,” but this is not true .

i.e. this was expected:

 SELECT * FROM Cases 
 WHERE created_at BETWEEN the beginning of '2013-05-01' AND the end of '2013-05-01' 

but what really happens is:

 SELECT * FROM Cases 
 WHERE created_at BETWEEN '2013-05-01 00: 00: 00 + 00000 ' AND '2013-05-01 00: 00: 00 + 00000 '

This becomes equivalent:

 SELECT * FROM Cases WHERE created_at = '2013-05-01 00: 00: 00 + 00000 '

---

The problem is one of the BETWEEN views / expectations that makes BOTH include a lower value and higher values ​​in the range, but doesn’t magically create enter “start” or “end”.

BETWEEN should be avoided when filtering by date ranges.

Always use >= AND < instead

 SELECT * FROM Cases 
 WHERE (created_at > = '20130501' AND created_at < '20130502') 

parentheses are optional here, but may make a difference in more complex queries.

Answer 3

You need to do one of these two options:

• Include the time component in the between : ... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59' condition ... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59' (not recommended ... see last paragraph)
• Use inequalities instead of between . Note that then you will need to add one day to the second value: ... where (created_at >= '2013-05-01' and created_at < '2013-05-02')

My personal preference is the second option. In addition, Aaron Bertrand has a very clear explanation of why it should be used.

Answer 4

Just use the timestamp as the date:

 SELECT * FROM Cases WHERE date(created_at)='2013-05-01' 

Answer 5

I believe the best solution for comparing a date and time field with a date field is the following:

 DECLARE @StartDate DATE = '5/1/2013', @EndDate DATE = '5/1/2013' SELECT * FROM cases WHERE Datediff(day, created_at, @StartDate) <= 0 AND Datediff(day, created_at, @EndDate) >= 0 

This is equivalent to an inclusive statement between, because it includes both the start and end dates, and those that fall between them.

Answer 6

 cast(created_at as date) 

This will only work in 2008 and later versions of SQL Server.

If you are using an older version, use

 convert(varchar, created_at, 101) 

Answer 7

You can use the date() function which will extract the date from the date and time and give you the result as including the date:

 SELECT * FROM Cases WHERE date(created_at)='2013-05-01' AND '2013-05-01' 

Answer 8

As an alternative:

 SELECT * FROM Cases WHERE convert(varchar, created_at, 120) Like '2013-05-01%'