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'

and

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?

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

more