CODEWITHSUNDEEP
sqlsql-server
Jah
Jah

Reputation: 1006

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'

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?

Upvotes: 2

Views: 2927

Answers (4)

Eric
Eric

Reputation: 3257

If the type of your column dates is DATETIME, the query

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

only pick up records that have dates between 2016-01-01 00:00:00.000 and 2016-01-02 00:00:00.000. It will not pick up the records that have dates > 2016-01-02 00:00:00.000. If you want to the query to pick up all records until 2016-01-02 23:59:59.999, then you will have to use your this query:

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

Upvotes: 0

Matt
Matt

Reputation: 14341

I agree with @GordonLinoff and was devising an example to show you so I will put it here anyway for you. note you can also just drop off the time component from your field by casting to a date and it will work.

WHERE CAST([date] as DATE) BETWEEN '2016-01-01' and '2016-01-02'

Example so it is apparent

DECLARE @Data AS TABLE (pid INT, name VARCHAR(10), [date] date, [datetime] DATETIME)
INSERT INTO @Data (pid, name, [date], [datetime])
VALUES
(1, 'test1', '2016-01-02', '2016-01-02')
,(2, 'test2', '2016-01-01', '2016-01-01')
,(3, 'test3', '2016-01-02', '2016-01-02')
,(3, 'test3', '2016-01-02', '2016-01-02 11:00:00.000')

SELECT *
FROM
    @Data
WHERE
    [date] BETWEEN '2016-01-01' and '2016-01-02'

SELECT *
FROM
    @Data
WHERE
    [datetime] BETWEEN '2016-01-01' and '2016-01-02'


SELECT *
FROM
    @Data
WHERE
    CAST([datetime] AS DATE) BETWEEN '2016-01-01' and '2016-01-02'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 7

Apostolos
Apostolos

Reputation: 10463

Are your columns datetime or just dates? did you try with convert functions?

e.g.

CONVERT(VARCHAR(10),dates,110)  --> 16-06-2016

i guess you have datetime columns which means that comparing with today's date, if the column is today then >= will always return true and <= will always return false, because timestamp being persisted to DB will probably be >= of today 00:00:00

Upvotes: 0

Related Questions