how to get last date of today in datetime datatype?
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 &gt;= @today
AND date_column &lt; 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 DATETIME or 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