Get DateTime with time as 23:59:59

I’m trying to do a where statement that specifies a DateTime field is between the start and end of the previous month.

To do this, I need to specify that the first day of the previous month has a time of 00:00:00 and the last day of the previous month has a time of 23:59:59.

This second condition is giving me a headache..

Can someone help me out?

Cheers

MSSQL 2008

Answer

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