sqlsql-serverdatesmalldatetime

SQL Server 2014 insert/update smalldatetime value with seconds

I'm having a strange issue with the smalldatetime data type in SQL Server.

I have a very basic table

create table datetest (
    value   smalldatetime   not null
)

And when I run the following

insert into datetest
values ('2016-12-29 21:30:00');

I see the value is 2016-12-29 21:30:00

Then when I run the following

update datetest
set value = '2016-12-29 21:31:30'

I see the value is 2016-12-29 21:31:00

It did not include the seconds. Why is this?

Solution

  • This is happening because precision of smalldatetime is 1 minute. It discards any seconds in datetime value by rounding off. For e.g: '2014-10-10 12:13:29' is rounded off to '2014-10-10 12:13:00' and '2014-10-10 12:13:30' is rounded off to '2014-10-10 12:14:00'