sql select date range returns date outside range

Question

Selecting aload of records based on dates, but im getting dates returned which are not inside the range any ideas?

SELECT *
FROM TABLE
WHERE (
        PAYMENT_DATE >= CONVERT(DATETIME, '2010-05-06')
        AND PAYMENT_DATE <= CONVERT(DATETIME, '2013-11-11')
        )

This for example will return a record that has a date of "2008-04-10 00:00:00.000"

May be the date format is taken as YYYY-DD-MM instead of YYYY-MM-DD — ngrashia, May 30 '14 at 07:37
Added `sql-server` tag based on usage of `convert` and `datetime` — , May 30 '14 at 07:49
[I Cannot replicate this problem with any DATETIME or VARCHAR](http://sqlfiddle.com/#!3/c604a8/1) - Is this the full query or do you have other predicates, possibly including an `OR` somewhere that means that the where clause isn't doing exactly what you think it is? — GarethD, May 30 '14 at 08:03
Is the data type of PAYMENT_DATE actually `DATETIME` or something similar? — NickyvV, May 30 '14 at 09:13
Note that inclusive upper-bounds (`<=`) aren't recommended for positive ranges, because you can get [incorrect results](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). This applies for all continuous-range types on all systems. This likely doesn't completely fix your problem though. — Clockwork-Muse, May 30 '14 at 13:51

score 2 · Answer 1 · edited May 30 '14 at 07:49

2

When you're not using parameters, always use the invariant datetime format:

 SELECT *
   FROM [Table]
  WHERE ([PaymentDate] >= '20100506' AND [PaymentDate] <= '20131111')

edited May 30 '14 at 07:49

Kevin Hogg

1,771
25
34

answered May 30 '14 at 07:47

Luaan

62,244
7
97
116

@GarethD Yes, but it can be converted wrong. For example, setting the language to german or czech will swap days and months. This still doesn't explain the `2008-04-10 00:00:00.000` but it does screw up the comparisons - `2010-04-10` is now bigger than `2010-05-06`, not good. – Luaan May 30 '14 at 08:24
2

@D-W You'll have to add more information. Add a sample table definition, sample data, sample query and make sure the error appears. SQL fiddle is very handy for this as well. – Luaan May 30 '14 at 08:49

score 1 · Answer 2 · edited May 30 '14 at 07:49

1

Its good to check the range with SQL between Clause.

 SELECT *
   FROM table
  WHERE PAYMENT_DATE BETWEEN CONVERT(datetime, '2010-05-06')
                         AND CONVERT(datetime, '2013-11-11')

edited May 30 '14 at 07:49

Kevin Hogg

1,771
25
34

answered May 30 '14 at 07:42

Ishtiaq

980
2
6
21

4

I would disagree about using BETWEEN for reasons in this article - [What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – GarethD May 30 '14 at 07:51
`BETWEEN` or an inclusive upper-bound (`<=`) for _any_ positive continuous-range type/data (prices, weights, etc). – Clockwork-Muse May 30 '14 at 13:58

score 0 · Answer 3 · edited May 30 '14 at 07:48

0

Just give a try to this

this must give the expected result

 SELECT *
   FROM table
  WHERE (Convert(datetime,PAYMENT_DATE,103) >= CONVERT(datetime, '2010-05-06') 
    AND Convert(datetime,PAYMENT_DATE,103) <= CONVERT(datetime, '2013-11-11'))

edited May 30 '14 at 07:48

Kevin Hogg

1,771
25
34

answered May 30 '14 at 07:38

Trikaldarshiii

11,174
16
67
95

1

#winces# converting `PAYMENT_DATE` is going to cause the system to ignore indices, meaning it will be slow on large datasets. Assuming the column is either a timestamp of some sort or a properly SARG-able character column it shouldn't be necessary here (although it would still be good to convert a character-based column anyways). – Clockwork-Muse May 30 '14 at 13:54

score 0 · Answer 4 · answered May 30 '14 at 07:39

0

Also, you can specify the month by its name or abbreviation, like below:

SELECT *
FROM TABLE
WHERE (
            PAYMENT_DATE >= CONVERT(DATETIME, '2010-May-06')
        AND PAYMENT_DATE <= CONVERT(DATETIME, '2013-Nov-11')
        )

Or you can just use the BETWEEN clause to simplify the query:

SELECT *
FROM TABLE
WHERE 
    PAYMENT_DATE BETWEEN '2010-May-06' AND '2013-Nov-11'

answered May 30 '14 at 07:39

Radu Gheorghiu

20,049
16
72
107

between is not cost effective – Trikaldarshiii May 30 '14 at 09:38
@NarendraModi-PMofINDIA Are we going for efficiency here or just to explain what might have gone wrong? – Radu Gheorghiu May 30 '14 at 10:51
@NarendraModi-PMofINDIA - `BETWEEN` is essentially syntactic sugar for the inclusive-range from, so it shouldn't cause any performance issues. Granted, it [should be avoided for other reasons](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common), but that means the inclusive upper-bound should be changed too. – Clockwork-Muse May 30 '14 at 13:57

sql select date range returns date outside range

4 Answers4