When dealing with dates, remember there is not such field as date in SQL Server 2005/2008. There is datetime field.
For reference there is a date type in MySQL which I think is very useful.
There could be problems when you use between dates in SQL Server 2008. The problem comes because datetime has time information also and that give a new meaning when you are doing comparision.
Lets get the current date.
select getdate() 2012-04-10 17:21:12.090 -- output
If I do
select getdate() as today where GETDATE()='2012-4-10' -- I get zero result
This is because when comparing dates, sql server 2008/2005 compares time information also. if the time information is 00:00:00, in that case it will print the date but that is not the case. Therefore it does not finds a match.
Using between with dates
Keep this information, if there is no time information, everything will be simple but if there is time information, it gives a new meaning to the day.