SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision
- 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
Following is the result of the above script and note that any value between 0 (zero) and 59 is converted up or down.
The part that confuses the developers is the value of the seconds in the display. I think if it is not maintained or recorded, it should not be displayed as well.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
