SQL Server : not getting last date with between query

Question

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?

What is the field type of `Dates`? – JNevill Jun 16 '16 at 16:31 — JNevill, Jun 16 '16 at 16:31

score 7 · Accepted Answer · edited Jul 29 '20 at 18:42

7

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

edited Jul 29 '20 at 18:42

Community

1
1

answered Jun 16 '16 at 16:35

Gordon Linoff

1,242,037
58
646
786

Thank you for your examples too :) – Jah Jun 16 '16 at 16:52
There is a [question](http://stackoverflow.com/questions/37866094/why-sometimes-there-is-one-of-more-gaps-in-the-value-of-auto-increment-column) which nobody cannot write an answer for it .. please take a look at it, maybe you can ... – stack Jun 16 '16 at 19:08
@Gordon Linoff, I think SQL Server 2008+ optimizer will use indexes for cast columns. – RGS Jun 17 '16 at 04:25
@RGS . . . I thought I made that point in the parenthetical comment. – Gordon Linoff Jun 19 '16 at 16:48

score 2 · Answer 2 · answered Jun 16 '16 at 16:40

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'

CAST([datetime] AS DATE) works perfectly and thank you for the examples — Jah, Jun 16 '16 at 16:51

score 0 · Answer 3 · answered Jun 16 '16 at 16:32

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

score 0 · Answer 4 · answered Jun 16 '16 at 16:51

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'

i really did not want to add the extra date but thank you. – Jah Jun 16 '16 at 16:52 — Jah, Jun 16 '16 at 16:52

SQL Server : not getting last date with between query

4 Answers4