Jah Jah - 2 years ago 51
SQL Question

SQL Server : not getting last date with between query

What am I doing wrong? I have an query to get data between two dates, but it does not get the last date.

I tried

select *
from data
where dates between '2016-01-01' and '2016-01-02'


select *
from data
where dates >= '2016-01-01' and dates <= '2016-01-02'

I need SQL Server to return data between 2016-01-01 and 2016-01-02

Output should be

pid name date
1 test2 2016-01-02
2 test2 2016-01-01
3 test3 2016-01-02

This works

select *
from data
where dates >= '2016-01-01' and dates <= '2016-01-03'

but why do I need to add an extra day?

Answer Source

Presumably your column (mis)named dates has a time component. This should work for what you want:

select *
from data
where dates >= '2016-01-01' and dates < '2016-01-03';

Aaron Bertrand, a SQL Server guru, has an excellent blog post on this subject, What do BETWEEN and the devil have in common?. That is a good place to start.

Alternatively, you could write the query as:

select *
from data
where cast(dates as date) >= '2016-01-01' and dates <= '2016-01-02';

Once you remove the time component, the comparisons will work. However, I'm reluctant to use functions on columns, because that can preclude the use of indexes in the query (SQL Server makes an exception for conversion to date).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download