CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldate
JBurace
JBurace

Reputation: 5633

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.

Upvotes: 190

Views: 260457

Answers (10)

Gonçalo Peres
Gonçalo Peres

Reputation: 13582

Even though it is inclusive, as per Aaron Bertrand suggestion (read more about it here)

don't use BETWEEN for date/time ranges.

Apart from other good answers around here, one can also use DATEADD (to add a number of days to a given date) as follows

SELECT * FROM Cases WHERE created_at >= '2013-05-01' AND created_at < DATEADD(day, 1, '2013-05-01')

Upvotes: 3

Yusuf Khan
Yusuf Khan

Reputation: 4050

your code

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

how SQL reading it

SELECT * FROM Cases WHERE '2013-05-01 22:25:19' BETWEEN '2013-05-01 00:00:00' AND '2013-05-01 00:00:00'

if you don't mention time while comparing DateTime and Date by default hours:minutes:seconds will be zero in your case dates are the same but if you compare time created_at is 22 hours ahead from your end date range

if the above is clear you fix this in many ways like putting ending hours in your end date eg BETWEEN '2013-05-01' AND ''2013-05-01 23:59:59''

OR

simply cast create_at as date like cast(created_at as date) after casting as date '2013-05-01 22:25:19' will be equal to '2013-05-01 00:00:00'

Upvotes: 3

Muralidharan C
Muralidharan C

Reputation: 71

Dyamic date BETWEEN sql query

var startDate = '2019-08-22';
var Enddate = '2019-10-22'
     let sql = "SELECT * FROM Cases WHERE created_at BETWEEN '?' AND '?'";
     const users = await mysql.query( sql, [startDate, Enddate]);

Upvotes: -1

Barranka
Barranka

Reputation: 21047

You need to do one of these two options:

  1. Include the time component in your between condition: ... where created_at between '2013-05-01 00:00:00' and '2013-05-01 23:59:59' (not recommended... see the last paragraph)
  2. Use inequalities instead of between. Notice that then you'll have 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. Also, Aaron Bertrand has a very clear explanation on why it should be used.

Upvotes: 22

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 390

Ted
Ted

Reputation: 91

I find that the best solution to comparing a datetime field to 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 between statement as it includes both the start and end date as well as those that fall between.

Upvotes: 9

abhishek kumar
abhishek kumar

Reputation: 466

You can use the date() function which will extract the date from a datetime and give you the result as inclusive date:

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

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35583

It has been assumed that the second date reference in the BETWEEN syntax is magically considered to be the "end of the day" but this is untrue.

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 happen is this:

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

Which becomes the equivalent of:

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

The problem is one of perceptions/expectations about BETWEEN which does include BOTH the lower value and the upper values in the range, but does not magically make a date the "beginning of" or "the end of".

BETWEEN should be avoided when filtering by date ranges.

Always use the >= AND < instead 

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

the parentheses are optional here but can be important in more complex queries.

Upvotes: 81

Balinti
Balinti

Reputation: 1534

Just use the time stamp as date:

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

Upvotes: 10

Ali Adravi
Ali Adravi

Reputation: 22723

cast(created_at as date)

That will work only in 2008 and newer versions of SQL Server

If you are using older version then use 

convert(varchar, created_at, 101)

Upvotes: 3

Related Questions