SQL “between” not inclusive

I have a query like this:

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

But this gives no results even though there is data on the 1st.

created_at looks like 2013-05-01 22:25:19, I suspect it has to do with the time? How could this be resolved?

It works just fine if I do larger date ranges, but it should (inclusive) work with a single date too.

Answers


It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

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

Another way to fix it is with explicit binary comparisons

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

Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.


Need Your Help

How to convert decimal array to string array in C#?

c# arrays

i need to convert Decimal array to string array . How to convert decimal[] to string[] ? Can i use

C++: POD and POD-wrapping objects

c++ pod

Often I declare classes to wrap a single Plain Old Data value; simple classes without virtual functions, like:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.