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.

Dogmatist

Depends on what version of SQL Server you're using, and what datatype your dates column is .... – Terrigenous

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) – Terrigenous

I think this link help to solve you problem: #2463238 – Amphistylar

Answer 1

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?

Emileemilee

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

@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!) – Cortezcortical

@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... – Rendarender

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 – Cortezcortical

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) – Cortezcortical

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 – Rendarender

Answer 2

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

Shippee

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

Given enough eyeballs, all problems become shallow ... :-) – Terrigenous

@marc Did you mean the other way round? It doesn't work for DATETIME2. See select cast('20120101 12:34:56.9999999' as datetime2) – Berkowitz

@RichardTheKiwi: yes - Oded originally had ... 23:59:59.999 which works for DATETIME2 (but not for DATETIME) – Terrigenous

Answer 3

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.

Berkowitz

Answer 4

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.

Cenacle

Answer 5

Try this:

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

Lowelllowenstein

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! – Terrigenous

Answer 6

try to use following query

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

Shop

Answer 7

Yes, it won't fetch you the second date. You can overcome this issue by adding second date in OR Condition.

Example:

select * 
from xxx 
where dates between '2012-10-26' and '2012-10-27' OR dates in('2012-10-26','2012-10-27')

Houchens

Thank you for your interest in contributing to the Stack Overflow community. This question already has a few answers—including some that have been validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Conform