HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

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

Submitted by: @import:stackexchange-dba··
0
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

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

Context

StackExchange Database Administrators Q#333784, answer score: 10

Revisions (0)

No revisions yet.