CODEWITHSUNDEEP
sql-server-2008datetime
Satinder singh
Satinder singh

Reputation: 10208

Sql Server where clause between two dates not gives expected output

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

Upvotes: 4

Views: 86741

Answers (4)

user7864457
user7864457

Reputation:

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

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

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

Upvotes: 15

Justin
Justin

Reputation: 9724

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 |

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460238

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

Upvotes: 2

Related Questions