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

Question

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

What does `23:30` have to do with your code sample? What, precisely, is your question? — HABO, Sep 22 '20 at 03:47

score 1 · Answer 1 · answered Sep 21 '20 at 20:04

1

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?

answered Sep 21 '20 at 20:04

Philip Kelley

39,426
11
57
92

score 0 · Answer 2 · answered Sep 21 '20 at 19:55

0

How about adding 30 minutes and converting to a date?

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

answered Sep 21 '20 at 19:55

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Sep 21 '20 at 20:13

0

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

answered Sep 21 '20 at 20:13

luisvenezian

441
5
18

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

3 Answers3