Add Time 23:59:59.999 to End Date for Between

ADD time 23:59:59.999 to end date for between

One option that avoids needing to add EndDate + 23:59:59.999 is to not use the between comparison and instead use column_name >= @StartDate and column_name < @EndDate +1

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

Why does DATEDIFF treat 23:59.59.999 as the next day?

MS SQL Uses
4 bytes to store the days past Jan 1, 1900.
4 bytes to store the clock ticks past midnight. ( a tick is 3.3miliseconds)

What you are seeing is overflow. Since the number can not be represented by the 4 bytes, it will use the 5th byte thus incrementing the day by 1.

SQL Server stores the second integer for the time as the number of
clock ticks after midnight. A second contains 300 ticks, so a tick
equals 3.3 milliseconds (ms). You can see the values for days and
clock ticks by converting a datetime value to a binary(8) value and
using the substring function to extract each set of 4 bytes. The code
in Figure 3 then converts each set of 4 bytes into an integer.

Source: http://www.itprotoday.com/microsoft-sql-server/solving-datetime-mystery

Why CAST('20140904 23:59:59.999' AS datetime) equals '2014-09-05 00:00:00.000'?

datetime values are rounded to increments of .000, .003, or .007 seconds

I think you should use

SELECT CAST('20140904 23:59:59.997' AS datetime)

This returns:

 2014-09-04 23:59:59.997

You can find more information here: http://msdn.microsoft.com/en-us/library/ms187819.aspx

This is the accuracy of datetime function in SQL.

You could probably use datetime2 if you are using a newer version of MSSQL as the accuracy for that is 100 nanosecs.

Datetime2: http://msdn.microsoft.com/en-us/library/bb677335.aspx

Convert DateTime Time from 00:00:00 to 23:59:59

DECLARE @Time TIME = '23:59:59.999'
SELECT dateColumn + @Time
FROM tableName

SQL Fiddle Demo

Edit

Cast @time to datetime before (+)

DECLARE @Time TIME = '23:59:59.999'
SELECT dateColumn + CAST(@Time as DATETIME)
FROM tableName

Get the max possible time of a date SQL Server

Turn it into a date to truncate the time, add a day, turn it back to a datetime, subtract a millisecond...

declare @Test datetime2(3) = '2021-02-16 13:08:58.620';

select dateadd(millisecond, -1, convert(datetime2(3),dateadd(day, 1, convert(date, @Test))));

---