Some useful sites:
To compare dates, this seems like the best method:
Get the start date by:
Replace [datetime] with the date (eg.
GETDATE() for the current date).
This casts the datetime as a float, gets the floor (chops off the decimal) and converts back to a date. This will always give you midnight for the day. Use a greater than or equal to comparison, as it needs to include midnight.
To get the end date:
Again, replace [datetime] with the date. This will return midnight on the following day, so use a less than comparison. This method seems to be slightly better than converting to a varchar, as it will not accidentally compare dates lexigraphically (comparing each digit separately).