3

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
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
Eric
  • 212
  • 2
  • 15
  • 2
    You'll run into problems because the `DATETIME` datatype in SQL Server has an accuracy of only 3.33ms - so `23:59:59.999` will be rounded up to the next day - the max value that you can represent as a `DATETIME` is `23:59:59.997`. Best solution would be to use `DATETIME2(n)` datatype instead which has an accuracy of as little as 100 ns ..... – marc_s Jun 07 '21 at 20:36
  • 2
    Just use LESS THAN and midnight the next day... – Caius Jard Jun 07 '21 at 20:36
  • @marc_s thanks for the comment never knew about the DATETIME2(n) very good information – Eric Jun 07 '21 at 20:42
  • 2
    Please explain why you want this. If this is so you can use `<= end of the day`, don't..... Instead use `< next day` – Nick.Mc Jun 08 '21 at 04:06

4 Answers4

6

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
2

Another simply way would be to cast your datetime to a date and just concatenate it with the maximum possibly time for your given precision. If you want accuracy to 3 decimal places you need to use datetime2(3)

declare @date datetime='20210216 13:08:58.620';

select Convert(datetime2(3),Concat(convert(date,@date),' 23:59:59.999'));
Stu
  • 30,392
  • 6
  • 14
  • 33
2

Just another option via format()

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

select convert(datetime2(3),format(@Test,'yyyy-MM-dd 23:59:59.999'))
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Hmm, I'm not sure I would use string parsy anything for date situations that could be done numerically.. – Caius Jard Jun 08 '21 at 06:17
  • 1
    @CaiusJard It is just an option.... another path from A to B... another tool in the belt... Depending on the use case, I may not use this either. It is 2 functions vs. 4, and the outer may be optional. Besides, sometimes you just need a hammer. It may not be pretty or sophisticated, but it gets the job done :) – John Cappelletti Jun 08 '21 at 13:22
2

For datetime datatype, the maximum value possible for time is : 23:59:59.997

Datetime from MSDN

We can use DateTimeFromParts to derive maximum possible datetime value for the day.

DECLARE @datevalue datetime = '2021-02-16 13:08:58.620'

SELECT DATETIMEFROMPARTS ( year(@datevalue), month(@datevalue), day(@datevalue), 23, 59, 59, 997 )  as maxPossibleValueForDay
maxPossibleValueForDay
2021-02-16 23:59:59.997
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58