Hello
just see this
I have table that names TestTable and it has these many data
1 Tejas 2011-09-19 14:54:11.613
2 Chirag 2011-09-12 14:54:11.633
3 Ankit 2011-09-23 14:54:11.633
4 Sagar 2011-09-15 14:54:11.633
5 Hiren 2011-09-28 14:54:11.633
now if i fire this query it will result this
SELECT * FROM TestTable WHERE date between '2011-09-12' and '2011-09-19' ORDER BY date
2 Chirag 2011-09-12 14:54:11.633
4 Sagar 2011-09-15 14:54:11.633
but in actual there is also a record with name as tejas with 2011-09-19 date,
actually it will be there in the above query result but it is not there
why???
because the column date hase data type as datetime so it will store a date with time in hh:mm:ss:tt format for cheking you can fire this query
print cast('2011-09-12' as datetime)
result is
Sep 12 2011 12:00AM
so if we want the 2011-09-19 record will be seen in the query we want to give time also, but that time is greater then the record's time when it is inserted like for 2011-09-19 has the time of insertion is 14:54:11.613
so for getting that record we have to give a time like 14:55 or 15:00
see this query is..
SELECT * FROM TestTable WHERE date between '2011-09-12' and '2011-09-19 14:55' ORDER BY date
result is
2 Chirag 2011-09-12 14:54:11.633
4 Sagar 2011-09-15 14:54:11.633
1 Tejas 2011-09-19 14:54:11.613
now you understand why you cant get the desired result in your query..
hope this will help full to you... and don't forgot to accept the solution and also rate it..