SQL Server : fetching records between two dates?

Question

In SQL I write a SELECT statement to fetch data between two dates, using between and

Ex:

select * 
from xxx 
where dates between '2012-10-26' and '2012-10-27'

But the rows returned are for 26th only, not 26th and 27th.

Can you help me? Thank you.

Depends on **what version** of SQL Server you're using, and **what datatype** your `dates` column is .... — marc_s, Oct 27 '12 at 10:49
Also: while you're at it - I would recommend to use a **safe** date/time format - one that works on all SQL Server versions, and independent of any date, regional or language settings. This would be either `20121017 23:59:59.997` (`YYYYMMDD` and **no dashes** for the date!) - or then `2012-10-17T23:59:59.997` (dashes in the date, and a fixed **`T`** as separator between date and time portions) — marc_s, Oct 27 '12 at 10:57
I think this link help to solve you problem: http://stackoverflow.com/questions/2462738/search-between-dates-and-times-in-sql-server-2008 — Campinho, Apr 17 '14 at 01:37

score 18 · Answer 1 · edited Jan 18 '21 at 12:30

18

As others have answered, you probably have a DATETIME (or other variation) column and not a DATE datatype.

Here's a condition that works for all, including DATE:

SELECT * 
FROM xxx 
WHERE dates >= '20121026' 
  AND dates <  '20121028'    --- one day after 
                             --- it is converted to '2012-10-28 00:00:00.000'
 ;

@Aaron Bertrand has blogged about this at: What do BETWEEN and the devil have in common?

edited Jan 18 '21 at 12:30

Community

1
1

answered Oct 27 '12 at 11:00

ypercubeᵀᴹ

113,259
19
174
235

2

The link to Aaron Bertrand's blog is very helpful because it explains why you're asking for problems when you use a BETWEEN query on datetime columns. I learned long ago to always say `between '1/1/2016 00:00:00' and 1/31/2016 23:59:59'` rather than `between '1/1/2016' and 1/31/2016'`, but never got around to exploring why. – James Toomey Jan 03 '17 at 23:58
@JamesToomey yes, the blog post is exceptional. You realize that your BETWEEN will fail if the type of the column allows milliseconds (and I do hope you aren't really using `m/d/yyyy` format in your date/datetime literals!) – ypercubeᵀᴹ Jan 04 '17 at 00:02
@TypoCubeᵀᴹ, I'm probably about to expose my ignorance, but I always appreciate learning something new--what is the danger with `m/d/yyyy` for querying? I like `yyyy/mm/dd` for screen output because it'll sort alphabetically if the person copies/pastes to a text file/whatever, but I suspect you're talking more along the lines of a risk of inaccurate data being returned from the query... – James Toomey Jan 04 '17 at 00:20
Correct, when it's for output, it doesn't matter, output in whatever format your users prefer. But for input (eg inserts, parameters in queries/procedures) there are certain issues. I have a few answers that deal with problems from ambiguous format but I'll refer you to another of Aaron's posts: [Bad habits to kick : mis-handling date / range queries](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) – ypercubeᵀᴹ Jan 04 '17 at 00:36
a literal like `'5/7/2016'` can be parsed as either 5th of July 2016 or 7th of May 2016, depending on various settings, giving incorrect (and perhaps unnoticed!) results in half of the cases. Or errors, for example with `'12/31/2016'` (is it 31st of Dec or 12th of the 31st month? It doesn't matter what you or I think but what the specific instance of SQL Server thinks, which depends on 3 or 4 language/database/connection settings) – ypercubeᵀᴹ Jan 04 '17 at 00:39
1

Thanks for the link! Very good point about `m/d/yyyy` vs `d/m/yyyy`. Being American I'm used to `m/d` but it never made much sense (shouldn't it go smallest to largest)? Scott Hanselman mentions discussing 9/11 as in Sept 11th and his German friend thinking he was referring to 9/11 as in the fall of the Berlin wall: ([link](http://www.hanselman.com/blog/OnTheNightmareThatIsJSONDatesPlusJSONNETAndASPNETWebAPI.aspx) – James Toomey Jan 04 '17 at 01:02

Oded · Answer 2 · 2012-10-27T10:50:23.970

13

You need to be more explicit and add the start and end times as well, down to the milliseconds:

select * 
from xxx 
where dates between '2012-10-26 00:00:00.000' and '2012-10-27 23:59:59.997'

The database can very well interpret '2012-10-27' as '2012-10-27 00:00:00.000'.

edited Oct 27 '12 at 10:50

answered Oct 27 '12 at 10:35

Oded

489,969
99
883
1,009

Actually, for a `DATETIME` column, you would need to specify `..23:59:59.997` since `DATETIME` has a 3ms resolution accuracy. For `DATETIME2` your solution will indeed work just fine. – marc_s Oct 27 '12 at 10:49
2

*Given enough eyeballs, all problems become shallow* ... :-) – marc_s Oct 27 '12 at 10:51
@marc Did you mean the other way round? It *doesn't* work for DATETIME2. See `select cast('20120101 12:34:56.9999999' as datetime2)` – RichardTheKiwi Oct 27 '12 at 11:02
2

@RichardTheKiwi: yes - Oded originally had `... 23:59:59.999` which works for `DATETIME2` (but not for `DATETIME`) – marc_s Oct 27 '12 at 11:21

RichardTheKiwi · Answer 3 · 2012-10-27T11:31:15.193

The unambiguous way to write this is (i.e. increase the 2nd date by 1 and make it <)

select * 
from xxx 
where dates >= '20121026'
  and dates <  '20121028'

If you're using SQL Server 2008 or above, you can safety CAST as DATE while retaining SARGability, e.g.

select * 
from xxx 
where CAST(dates as DATE) between '20121026' and '20121027'

This explicitly tells SQL Server that you are only interested in the DATE portion of the dates column for comparison against the BETWEEN range.

score 2 · Answer 4 · answered Sep 09 '15 at 18:46

Your question didnt ask how to use BETWEEN correctly, rather asked for help with the unexpectedly truncated results...

As mentioned/hinting at in the other answers, the problem is that you have time segments in addition to the dates.

In my experience, using date diff is worth the extra wear/tear on the keyboard. It allows you to express exactly what you want, and you are covered.

select * 
from xxx 
where datediff(d, '2012-10-26', dates) >=0
and  datediff(d, dates,'2012-10-27') >=0

using datediff, if the first date is before the second date, you get a positive number. There are several ways to write the above, for instance always having the field first, then the constant. Just flipping the operator. Its a matter of personal preference.

you can be explicit about whether you want to be inclusive or exclusive of the endpoints by dropping one or both equal signs.

BETWEEN will work in your case, because the endpoints are both assumed to be midnight (ie DATEs). If your endpoints were also DATETIME, using BETWEEN may require even more casting. In my mind DATEDIFF was put in our lives to insulate us from those issues.

krishnang · Answer 5 · 2012-10-27T14:02:33.120

1

Try this:

select * 
from xxx 
where dates >= '2012-10-26 00:00:00.000' and dates <= '2012-10-27 23:59:59.997'

edited Oct 27 '12 at 14:02

answered Oct 27 '12 at 10:41

krishnang

698
1
7
21

1

This will **not** select the rows with a `date` of `2012-10-27 23:59:59.003` through `2012-10-27 23:59:59.997` - there might be some in that date range, too! – marc_s Oct 27 '12 at 10:54

score 0 · Answer 6 · answered Oct 27 '12 at 20:11

0

try to use following query

select * 
from xxx 
where convert(date,dates) >= '2012-10-26' and convert(date,dates) <= '2012-10-27'

answered Oct 27 '12 at 20:11

Hiren Dhaduk

2,760
2
19
21

SQL Server : fetching records between two dates?

6 Answers6

Linked