Why on earth do you need the last possible value of today? Stop using
BETWEEN; it is much easier (and much more reliable, and consistent across various date/time data types) to use an open ended range:
DECLARE @today SMALLDATETIME;
SET @today = DATEADD(DAY, DATEDIFF(DAY, '20000101', GETDATE()), '20000101');
SELECT ... FROM ...
WHERE date_column >= @today
AND date_column < DATEADD(DAY, 1, @today);
The problem with what you're doing is that the data type can affect what happens. If the underlying data type is
SMALLDATETIME, it rounds up; if the underlying data type is
DATETIME2, subtracting a second is not going to capture all of the data from that day. This is actually one of my favorite slides in my T-SQL : Bad Habits and Best Practices talk. For a lot more details, please read these blog posts:
What do BETWEEN and the devil have in common?
Bad Habits to Kick : mis-handling date / range queries