Trent Trent - 18 days ago 10
SQL Question

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?

Answer

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'