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 (https://blog.sqlauthority.com)
