What I am trying to do is take a date in SQL Server and find the last possible hour, minute, second, and millisecond of that date.
So if the date is this: 2021-02-16 13:08:58.620
I would like to return: 2021-02-16 23:59:59.999
I have tried something like this but it is not working and I guess that I am missing something where it is converting the time and keeping the hour, minute, second and millisecond of that date
Select
DateAdd(MILLISECOND, -1, (Convert(datetime, DATEADD(day, 1, DateValue), 101))) as lastPossibleDate
From
Table1
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))));
Result |
---|
2021-02-16 23:59:59.999 |
Note if you use straight datetime
rather than datetime(2)
your accuracy is only down to 3ms. datetime(2)
is the recommended datetime datatype to use.