I was hoping that the following code:
declare @end_date as datetime
set @end_date = '2008-03-10 02:00:00.000'
SELECT DATEADD(ms,-1,DATEADD(mm, DATEDIFF(m,0,@end_date)+1,0))
would give me '2008-03-31 23:59:59.999'
but it doesn't, i get '2008-04-01 00:00:00.000'
I think this is because datetime is accurate to roughly 3 milliseconds so it rounds to the next day. If I change it to -2 ms....
SELECT DATEADD(ms,-2,DATEADD(mm, DATEDIFF(m,0,@end_date)+1,0))
I correctly get '2008-03-31 23:59:59.997'
So the question is: How do I accurately determine the last day in the month for a given date, to the last millisecond (999)?