DECLARE @TODAY DATETIME = CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),126))
SELECT @TODAY
DECLARE @TOMORROW DATETIME = @TODAY+1
--
-- Alternative
-- DECLARE @TOMORROW DATETIME = DATEADD(DD,1,@TODAY)
-- DECLARE @TOMORROW DATETIME = DATEADD(HH,24,@TODAY)
--
SELECT @TOMORROW
SELECT * FROM TheTable WHERE TheDate >= @TODAY AND TheDate < @TOMORROW
Or something similar to this.
Because time is a continuous and you do not want to depend on granulaty of the storage system, you want to check from a beginning time where the time is included up to a end time where the time is excluded.
Why?
In this way it does not matter if you express your time in months, weeks, days, hours, minutes etc.
For example if you want to check for an half an hour, you check for the greater or equal to the begin time and less then the begintime plus 30 minutes.
Do not try to create an endtime which is included, because this is very type dependend. For a day the last endtime which is stored in datetime format is 23:59:59.997. But for other formats it is different. While the next day 00:00:00 is available in all storage types (I strongly hope :-P)
(For shortdatetime it is 23:59:59 Me thinks).
ben brugman