2

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?

TheLovelySausage
  • 3,838
  • 15
  • 56
  • 106

4 Answers4

5

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'

Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20
  • Thanks a lot for the solution, the smalldatetime looks very misleading with the space for the seconds I would have been scratching my head for hours – TheLovelySausage Nov 22 '16 at 10:31
3

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/

Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • 1
    Although I need to add that if you are using more recent versions which support **`datetime2`** and you are implementing, then you should use it, instead of `datetime`. – Radu Gheorghiu Nov 22 '16 at 10:10
  • Absolutely, that's a very good point. I work for a vendor that supports back to 2008 so can't take advantage of that yet. – Rich Benner Nov 22 '16 at 10:12
3

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

A.Goutam
  • 3,422
  • 9
  • 42
  • 90
  • They're not set to zero though, if they're 59 seconds then it doesn't just change the seconds to zero and use that value. It rounds to the nearest minute (which, in this case, would be up a minute) – Rich Benner Nov 22 '16 at 10:22
  • @RichBenner i have made changes in my post – A.Goutam Nov 22 '16 at 10:30
1

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

rbr94
  • 2,227
  • 3
  • 23
  • 39