Why is SQL server dropping the seconds of values passed to a SMALLDATETIME field?

Question

I'm using

Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64)
Jul 27 2023 18:11:00 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: )

I have a table I created with

CREATE TABLE yokogawaReading(
yokogawaReading INTEGER NOT NULL IDENTITY (1, 1),
readingDate SMALLDATETIME not null,
celsiusTemperature1 DECIMAL (4,2),
relativeHumidity1 DECIMAL (5,3),
celsiusTemperature2 DECIMAL (4,2),
relativeHumidity2 DECIMAL (5,3)
);

Whenever I try to insert a record where the value for the field readingDate includes a nonzero seconds value, e.g.,

EXEC sp_set_session_context @key = N'readingDateTime', @value = '2023/11/14 11:09:30';
EXEC sp_set_session_context @key = N't1', @value =20.027777777777777777777777778;
EXEC sp_set_session_context @key = N'rh1', @value =44.97;
EXEC sp_set_session_context @key = N't2', @value =18.916666666666666666666666667;
EXEC sp_set_session_context @key = N'rh2', @value =38.485;
"INSERT INTO yokogawaReading (readingDate, celsiusTemperature1, relativeHumidity1, celsiusTemperature2, relativeHumidity2) VALUES (CONVERT(datetime, SESSION_CONTEXT(N'readingDateTime'), 101), CONVERT(decimal(7, 2), SESSION_CONTEXT(N't1')), CONVERT(decimal(5, 2), SESSION_CONTEXT(N'rh1')), CONVERT(decimal(7, 2), SESSION_CONTEXT(N't2')), CONVERT(decimal(5,2), SESSION_CONTEXT(N'rh2')));"

The resulting record ends up with a time that has '00' for the seconds. I checked the documentation on SMALLDATETIME, and it definitely includes seconds, though not milliseconds. Why is this happening and how can I get around it?

[Addendum] I think I may have found the answer to the why question on this forum question. (See TG's response on 2011年10月26日 : 17:37:20.) TG says

Sql server stores smalldatetime as a pair of 2 byte smallints. The first smallint is some sort of day offset of 1900年01月01日. The second smallint is a minute offset from 0:00:00.

If that's accurate then it can't store seconds and the documentation I mentioned above has to be incorrect. Is it?

• betelgeuce: Is there any specific reason why you could not use datetime2 instead of smalldatetime to store the date and times?
• agerber85: Thanks for the suggestion! I don't like using data types that are overkill (for resource efficience reasons). At first blush, datetime2 looked like overkill because, in this case, I don't need nanoseconds or even milliseconds. As I look closer, I think datetime2(0) would be exactly what I need.

Answer 1

The documentation you've linked to says explicitly that seconds for a smalldatetime is always zero:

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

and later in the description:

Accuracy: One minute

• agerber85: Thanks! Perhaps this discussion isn't appropriate here, but isn't it a bit confusing that they say the time range is '00:00:00 through 23:59:59', also that queries of smalldatetime fields have results that show the seconds portion?
• BradC: @agerber85 even that portion of the document includes the following clarification: "ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute. Values of 29.999 seconds or more are rounded up to the nearest minute."
• Barmar: @BradC Out of curiosity, is there a rationale for the rounding break to be at 29.999 rather than 30.000? It's a curious asymmetry.
• BradC: @agerber85 I agree, to some degree. Perhaps the page should be clarified to say something like the smalldatetime accepts inputs with ss between 00 and 59, but rounds them to the nearest minute when storing the value.
• Martin Smith: @Barmar - maybe because the datetime datatype supports 300 ticks per seconds. with milisecond values ending 0, 3, 7 - 29.998 would round to 29.997 when assigning to datetime

Answer 2

Smalldatetime : Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute. Values of 29.999 seconds or more are rounded up to the nearest minute.

That means

Declare @i smalldatetime='2023-12-25 14:03:03' or '2023-12-25 14:03:29'
Output '2023-12-25 14:03:00'
set @i smalldatetime='2023-12-25 14:03:31' or '2023-12-25 14:03:59'
Output '2023-12-25 14:04:00'
When second is 29 or below 29 second theen output round to 03 minute.
When second is 30 or above 30 second theen output round to 04 minute.

Suggested Reading

If your requirmenet is to store exact second then you can also use Datetime2(0).It let yo store date+time The time has a range from 00:00:00 to 23:59:59.9999999. and also you can restrict to store only till second.That is user can define second precission in Datetime2

So in above example,

set @i datetime2(0)='2023-12-25 14:03:31' or '2023-12-25 14:03:59'

Output will be exactly same.