Sql Server where clause between two dates not gives expected output

Question

This is my simple query to fetch dates using 'between' operator.

create table dummy (mydate datetime);
 insert into dummy values('2013-03-20 10:30:00.000');
 insert into dummy values('2013-03-21 10:30:00.000');
 insert into dummy values('2013-03-22 10:30:00.000');
 insert into dummy values('2013-03-23 10:30:00.000');
 insert into dummy values('2013-03-24 10:30:00.000');
 insert into dummy values('2013-03-25 10:30:00.000');
 insert into dummy values('2013-03-26 10:30:00.000');
 insert into dummy values('2013-03-27 10:30:00.000');
 insert into dummy values('2013-03-28 10:30:00.000');
 insert into dummy values('2013-03-29 10:30:00.000');
 insert into dummy values('2013-03-30 10:30:00.000');

The qiuery I m using is: select * from dummy where mydate between ('3/01/2013 12:00:00 AM') and ('3/30/2013 12:00:00 AM')

Here 2013-03-30 10:30:00.000 , should also be returned.

SQL FIDDLE

Use the same (unambiguous) datetime format as your insert statements use. — Martin Smith, May 24 '13 at 09:14
@MartinSmith: I cnt change the insertion format `2013-03-30 10:30:00.000`, but yes in select query i can do changes — Satinder singh, May 24 '13 at 09:16
Yes that's what I'm talking about. The format in the insert is correct for all language/dateformat settings. — Martin Smith, May 24 '13 at 09:18
@MartinSmith - except that when it uses a space rather than `T`, it's still ambiguous. `2013-03-30T10:30:00` is unambiguous. If you run `set language british; select MONTH('2013-03-04 10:30:00')` you'll get `4` as a result. — Damien_The_Unbeliever, May 24 '13 at 09:18
@MartinSmith - Just was editing an example into my previous comment. — Damien_The_Unbeliever, May 24 '13 at 09:19
@Damien_The_Unbeliever `set language british; select MONTH('2013-03-04 10:30:00.000')` returns `3` — Martin Smith, May 24 '13 at 09:20
@MartinSmith - ah - it's a difference of versions. On 2005 and earlier, it returns 4. On 2008 and later it returns 3. So I still wouldn't recommend it as an "unambiguous" format. — Damien_The_Unbeliever, May 24 '13 at 09:23
@Damien_The_Unbeliever - Yes you're right `yyyymmdd hh:mm:ss` is the unambiguous one. http://www.karaszi.com/sqlserver/info_datetime.asp — Martin Smith, May 24 '13 at 09:24
@MartinSmith From SQL Server 2008 the string literal passed to `month` is treated as a `datetime2` [SQL Fiddle](http://sqlfiddle.com/#!3/d41d8/14271) — Mikael Eriksson, May 24 '13 at 09:28

Mikael Eriksson · Accepted Answer · 2013-05-24T09:30:23.120

15

Change the datetime format in your where clause and check the values with >= and < instead.

select * 
from dummy 
where mydate >= '20130301' and
      mydate < '20130401'

edited May 24 '13 at 09:30

answered May 24 '13 at 09:18

Mikael Eriksson

136,425
22
210
281

Thanks for reply, just want to know is their any difference using `>=` or `between` operator. – Satinder singh May 24 '13 at 09:32
3

@Satindersingh Yes there is a big difference. `between` is inclusive. [What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) – Mikael Eriksson May 24 '13 at 09:34
here you have used 4 month, and am using my enddate as 30 for 3 month. If adding one day will solve then it can be done with my existing query http://sqlfiddle.com/#!3/f7026/19 – Satinder singh May 24 '13 at 10:10
1

#ERROR! – Tim Schmelter May 24 '13 at 10:20
@Satindersingh then it will include values from the next day and I don't think you want that. You should read the article I linked to in the earlier comment. – Mikael Eriksson May 24 '13 at 10:46
@MikaelEriksson: Thank you verymuch, now onwards i will never use `BETWEEN` for `Datetime` datatype – Satinder singh May 24 '13 at 11:49

score 3 · Answer 2 · answered May 24 '13 at 09:15

Query:

SQLFIDDLEExample

select * 
from dummy 
where mydate between ('3/01/2013 12:00:00') 
and ('3/30/2013 12:00:00')

Result:

|                       MYDATE |
--------------------------------
| March, 20 2013 10:30:00+0000 |
| March, 21 2013 10:30:00+0000 |
| March, 22 2013 10:30:00+0000 |
| March, 23 2013 10:30:00+0000 |
| March, 24 2013 10:30:00+0000 |
| March, 25 2013 10:30:00+0000 |
| March, 26 2013 10:30:00+0000 |
| March, 27 2013 10:30:00+0000 |
| March, 28 2013 10:30:00+0000 |
| March, 29 2013 10:30:00+0000 |
| March, 30 2013 10:30:00+0000 |

score 2 · Answer 3 · answered May 24 '13 at 09:14

2

I assume(my culture has no AM/PM designator) that the 12 AM means midnight(so from 29th to 30th). So either use 12 PM or omit the AM/PM designator.

mydate between ('3/01/2013 12:00:00 AM') 
and ('3/30/2013 12:00:00 PM')

Demo

You could also remove the time part and add one day which means midnight:

mydate between '2013-03-01' and '2013-03-31'

Demo

answered May 24 '13 at 09:14

Tim Schmelter

450,073
74
686
939

@Satindersingh: Have you seen my demos? – Tim Schmelter May 24 '13 at 09:21
Thanks its wroking, So now have to pass date '2013-03-01' format from c# – Satinder singh May 24 '13 at 09:30
Curiosity want to know, as per Mikael Eriksson answer , using `>=` is good in my case, or should i go with urs – Satinder singh May 24 '13 at 09:41
@Satindersingh: That's a matter of taste, do what you find easier to read. – Tim Schmelter May 24 '13 at 09:45
1

@Satindersingh I would say it is a bit more then a matter of taste. [Here is a rant of mine in a comment to another question that explains a bit about the trouble you can have with `between`](http://stackoverflow.com/questions/14153837/datediff-or-between-for-date-ranges-in-sql-queries#comment19604719_14153977). – Mikael Eriksson May 24 '13 at 09:49

score 0 · Answer 4 · answered Apr 13 '17 at 21:29

Remember, date time data uses 24hour clock (00:00:00 to 23:59:59). The mistake is in the 'AM' designation in the condition '3/30/2013 12:00:00 AM', because 12:00:00 AM is equal to 00:00:00, and 10:30:00 is "Greater than" 12:00:00 AM, that's why the value '2013-03-30 10:30:00.000' it's not in the selected values.

Use select * from dummy where mydate between ('3/01/2013 12:00:00') and ('3/30/2013 12:00:00')

Sql Server where clause between two dates not gives expected output

4 Answers4

Linked