SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision
June 1, 2010 by pinaldave
I am myself surprised that I am writing this post today. I am going to present one of the very known facts of SQL Server SMALLDATETIME datatype. Even though this is a very well-known datatype, many a time, I have seen developers getting confused with precision of the SMALLDATETIME datatype.
The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero. Let us see the following example
DECLARE @varSDate AS SMALLDATETIME
SET @varSDate = '1900-01-01 12:12:01'
SELECT @varSDate C_SDT
SET @varSDate = '1900-01-01 12:12:29'
SELECT @varSDate C_SDT
SET @varSDate = '1900-01-01 12:12:30'
SELECT @varSDate C_SDT
SET @varSDate = '1900-01-01 12:12:59'
SELECT @varSDate C_SDT
1 comment:
According to INFORMATION_SCHEMA.COLUMNS, the smalldatetime values in our SS 2005 database have a DATETIME_PRECISION of 3. We are planning to port to SS 2014, and the corresponding columns there show a DATETIME_PRECISION of 0. Did we port columns incorrectly? Or is there just a change in the way INFORMATION_SCHEMA represents the same data?
Post a Comment