T-sql SmallDateTime add day when time passed 23:30

Question

T-sql SmallDateTime add day when time passed 23:30

declare @sampledate DateTime
set @sampledate='2020-09-18 23:59:53'
select  @sampledate AS Date,
       CAST(@sampledate as DATE) AS CAST_Date,
       CAST(@sampledate as SmallDateTime) CAST_smalldatetime,
   CONVERT(SmallDateTime,@sampledate) Convert_smalldatetime

Answer 1

How about adding 30 minutes and converting to a date?

convert(date, dateadd(minute, 30, @sampledate)

Answer 2

When converting datetime to smalldatetime, SQL will round the minute based on the seconds. It works out that 23:59:29.998 and below will round down to 23:59, and 23:59:29.999 and up will round up to tomorrow.

But does this answer your question?

Answer 3

This is the expected behaviour. Look at Property Time range from MS Docs.

2007-05-09 23:59:59 will round to 2007-05-10 00:00:00