patternsqlModerate
Why is SQL server dropping the seconds of values passed to a SMALLDATETIME field?
Viewed 0 times
whythevaluesfieldsmalldatetimesqldroppingsecondspassedserver
Problem
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
Whenever I try to insert a record where the value for the field
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 a
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 a
Solution
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
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
Context
StackExchange Database Administrators Q#333784, answer score: 10
Revisions (0)
No revisions yet.