Get DateTime with time as 23:59:59

try:

SELECT DATEADD(ms, -3, '2011-07-20')

This would get the last 23:59:59 for today.

why 3 milliseconds?, this is because Microsoft SQL Server DATETIME columns have at most a 3 millisecond resolution (something that is not going to change). So all we do is subtract 3 milliseconds

You can also use the less than '<' without the equal. So that you don't need 23:59:59.

Eg. WHERE DateCreated < '20111201 00:00:00'

Try this, it could be helpful for you I use one of these two ways to work with time portion in DATETIME fields to do comparisons EX: get a user log for one day, i.e. from Today's date at 12:00:00 AM till Today's date but at 12:00:00 PM

DECLARE @FromDate datetime
DECLARE @ToDate datetime

SET @FromDate = GETDATE()
SET @ToDate = GETDATE()
Print '------------------------ '
PRINT @FromDate
PRINT @ToDate
SET @FromDate = CONVERT(DATETIME, CONVERT(varchar(11),@FromDate, 111 ) + ' 00:00:00', 111) 
SET @ToDate = CONVERT(DATETIME, CONVERT(varchar(11),@ToDate, 111 ) + ' 23:59:59', 111)
Print '------------------------ '
PRINT @FromDate
PRINT @ToDate



DECLARE @TEST_FROM DATETIME
SET @TEST_FROM = dateadd(month,((YEAR(@FromDate)-1900)*12)+MONTH(@FromDate)-1,DAY(@FromDate)-1) + ' 12:00:00'

DECLARE @TEST_TO DATETIME
SET @TEST_TO = dateadd(month,((YEAR(@ToDate)-1900)*12)+MONTH(@ToDate)-1,DAY(@ToDate)-1) + ' 23:59:59'

Print '------------------------ '
PRINT @TEST_FROM
PRINT @TEST_TO

This will print the following in SQL Query editor screen

------------------------ 
Dec 28 2011  3:18PM
Dec 28 2011  3:18PM
------------------------ 
Dec 28 2011 12:00AM
Dec 28 2011 11:59PM
------------------------ 
Dec 28 2011 12:00PM
Dec 28 2011 11:59PM

References The way using the convert is from my experience, the other way is from this link http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx Have fun :)