SQL Server 2014 insert/update smalldatetime value with seconds

Question

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 1

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'

Answer 2

This is one of the characteristics of smalldatetime over datetime.

Microsoft documentation on smalldatetime

The main differentation is that it rounds to the nearest minute. If you want to see seconds (and milliseconds) then you need to consider the datetime data type.

In your example however, it should return the value 2016-12-29 21:32:00 because it rounds up from 30 seconds to the next minute. anything less than 30 seconds gets rounded down. Example;

CREATE TABLE #DateTest (ID int, DateValue smalldatetime)
INSERT INTO #DateTest (ID, DateValue)
VALUES
(1,'2016-12-29 21:31:29')
,(2,'2016-12-29 21:31:30')

SELECT * FROM #DateTest

Output

ID  DateValue
1   2016-12-29 21:31:00
2   2016-12-29 21:32:00

Some further reading links;

http://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/

http://sqlcoach.blogspot.co.uk/2007/08/sql-server-storing-time-coming-soon.html

http://sqlhints.com/2016/10/10/difference-between-smalldatetime-and-datetime-data-types-in-sql-server/

Answer 3

It rounds all seconds to minutes:

Time range:

00:00:00 through 23:59:59

2007-05-09 23:59:59 will round to

2007-05-10 00:00:00

see chapter smalldatetime Description: https://msdn.microsoft.com/en-us/library/ms182418(v=sql.120).aspx

Answer 4

When the conversion is to datetime, the smalldatetime value is copied to the datetime value. The fractional seconds will make next nearest minutes. The following code shows the results of converting a smalldatetime value to a datetime value.

 DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';  
DECLARE @datetime datetime = @smalldatetime;  

SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';  

--Result  
--@smalldatetime          datetime  
------------------------- -----------------------  
--1955-12-13 12:43:00     1955-12-13 12:43:00.000  

Take a look MSDN Link