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 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.

Well, how many numbers are between 1 and 1? Should 1.5 be between 1 and 1? [Just don't use BETWEEN for date/time ranges. Ever.](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx) And be careful how you evaluate "works just fine" - have you closely inspected the results from the last day in the range? You would only include all rows if they didn't have any time associated with them. — Aaron Bertrand, May 02 '13 at 21:11
Updated URL for Aaron: https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common — JayRizzo, Aug 30 '19 at 17:47

score 375 · Accepted Answer · edited Aug 26 '19 at 11:42

375

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.

edited Aug 26 '19 at 11:42

xOneca

842
9
20

answered May 02 '13 at 21:03

Gordon Linoff

1,242,037
58
646
786

18

For completeness sake on this good answer, I would suggest using `dateadd` to get the next day. – Tim Lehner May 02 '13 at 21:14
8

@TimLehner For the sake of a good answer, I would have used ISO-8601 format of '20130501'. For non-US people with dateformat dmy, you get this: `set dateformat dmy;select month(cast('2013-05-01' as datetime)); =1` – RichardTheKiwi May 02 '13 at 21:21
2

@RichardTheKiwi - I believe the _explicit_ bit is using an interval closed at one end (`>=`) and open at the other (`<`) combined with checking one day beyond the specified end date. – HABO May 02 '13 at 22:01
This sort of problem is why I choose to represent date/times internally as Modified Julian Day numbers. They are always with respect to UTC, in millisecond precision with just a double, and can be easily converted to displayable forms. Moreover, date arithmetic, sorting, comparisons, and date-based filters are trivial to implement. – scottb May 03 '13 at 01:16
3

@scottb Personally, I would find it annoying to have to convert to datetimes every single time I wanted to display, export, import, or write a class with a datetime. I think SQL Server has plenty of built-in functionality to manipulate and compare datetimes for most purposes. – Tim Lehner May 03 '13 at 13:19
@TimLehner: of course, the choice of how to represent date/times depends on many things. If date comparisons, arithmetic, or sorting are not an emphasis for a particular data set or application, or if you're stepping into a legacy environment which is already standardized on a particular representation then it makes sense to go with the flow. For new app development, the advantages of representing date/times internally as universal, UTC POSIX longs or Julian Day Numbers can be compelling and well worth consideration. This question speaks to just one issue with datetimes. – scottb May 03 '13 at 21:15
13

You should never cast a column in the `where` clause, since it will lose any indexing it has. It's a really bad pattern. – Buzinas Sep 05 '15 at 21:51
First one is perfect solution for dynamic date inputs.Thanks A lot. – Mohan Srinivas Dec 21 '17 at 11:21
what if the date is the last date of the month? how can I have a generic solution for every date of the year? – Farrukh Sarmad Feb 28 '18 at 07:42
2

@Buzinas . . . You comment specifically does not apply to casting to a date in SQL Server. – Gordon Linoff Feb 28 '18 at 12:34
1

@FarrukhSarmad . . . If you have a question, ask it as a question, not in a comment. – Gordon Linoff Feb 28 '18 at 12:34

Paul Maxwell · Answer 2 · 2017-08-21T23:57:30.540

74

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.

edited Aug 21 '17 at 23:57

answered Oct 04 '14 at 07:50

Paul Maxwell

33,002
3
32
51

2

Unsure about the wisdom of posting this well after the question has already been answered but I wanted to stress a slightly different point – Paul Maxwell Oct 04 '14 at 07:52
5

To editors; please don't try to make the pseudo SQL into code blocks, it simply does not work as the comment relies on BOLD. – Paul Maxwell Oct 30 '14 at 22:45
2

To editors (again) please, do not add spurious quotation marks through the pseudo code; they do NOT aid comprehension. – Paul Maxwell Jul 23 '15 at 07:29

score 22 · Answer 3 · edited Aug 30 '19 at 20:52

22

You need to do one of these two options:

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)
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.

edited Aug 30 '19 at 20:52

JayRizzo

3,234
3
33
49

answered May 02 '13 at 21:05

Barranka

20,547
13
65
83

6

+1 for 2. But -1 for 1. This end of day hack is completely unreliable and a bad idea. – Aaron Bertrand May 02 '13 at 21:09
1

@AaronBertrand I also prefer option 2 (I use it frequently). But why do you say option 1 is "completely unreliable"? – Barranka May 02 '13 at 21:31
6

[please read this in full](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx). You should never use `BETWEEN` for date range queries that include time; way too much can go wrong. – Aaron Bertrand May 02 '13 at 21:34
Although 2. works I will always write it as `where ('2013-05-01' <= created_at and created_at <= '2013-05-01')` for better readability – theking2 Aug 21 '21 at 08:44

score 10 · Answer 4 · answered Sep 12 '15 at 19:13

10

Just use the time stamp as date:

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

answered Sep 12 '15 at 19:13

Balinti

1,524
1
11
14

5

The question is tagged SQL Server, and `'DATE' is not a recognized built-in function name.` Need to convert http://stackoverflow.com/a/20973452/4233593 – Jeff Puckett Jan 25 '17 at 23:38

score 8 · Answer 5 · edited Jan 10 '19 at 09:06

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.

score 3 · Answer 6 · edited May 18 '15 at 12:18

3

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)

edited May 18 '15 at 12:18

shA.t

16,580
5
54
111

answered May 22 '14 at 19:19

Ali Adravi

21,707
9
87
85

2

`convert(varchar, created_at, 101)` result is like `dd/MM/yyyy` and OP's strings are `yyyy-MM-dd`, I think this answer will not work ;). – shA.t Apr 09 '15 at 11:49

score 2 · Answer 7 · edited Mar 22 '18 at 07:51

2

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'

edited Mar 22 '18 at 07:51

Thomas Flinkow

4,845
5
29
65

answered Mar 22 '18 at 07:15

abhishek kumar

448
4
10

score 2 · Answer 8 · answered Dec 10 '20 at 21:53

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'

score 1 · Answer 9 · answered Sep 26 '22 at 09:04

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')

score 0 · Answer 10 · answered Nov 22 '19 at 07:25

0

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]);

answered Nov 22 '19 at 07:25

Muralidharan C

73
4

SQL "between" not inclusive

10 Answers10

Linked