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

Answer 1

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

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

Answer 2

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 |