CODEWITHSUNDEEP
sql-serverdatetimedate-range
Ssasidhar
Ssasidhar

Reputation: 485

SQL Server : fetching records between two dates?

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.

Upvotes: 19

Views: 133652

Answers (7)

bhinu bhinusha
bhinu bhinusha

Reputation: 1

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

Upvotes: 0

greg
greg

Reputation: 1873

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.

Upvotes: 2

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

try to use following query

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

Upvotes: 0

krishnang
krishnang

Reputation: 698

Try this:

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

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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.

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

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?

Upvotes: 18

Oded
Oded

Reputation: 499382

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

Upvotes: 13

Related Questions