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. So let us look at the precision of SMALLDATETIME once and for all.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
6 Comments. Leave new
Nice article,
Just want to be more accurate about rounding,
example
DECLARE @varSDate AS SMALLDATETIME
SET @varSDate = ‘1900-01-01 12:12:29.998’
SELECT @varSDate C_SDT
— Will give you ‘1900-01-01 12:12:00’
SET @varSDate = ‘1900-01-01 12:12:29.999’
SELECT @varSDate C_SDT
— Will give you ‘1900-01-01 12:13:00’
Sir,
I’ve used this datatype so many times but i’ve not noticed the issue yet. Thanks for posting this issue…..
I’ve always defaulted to using the 4 byte smalldatetime instead of the 8 byte datetime, unless there is a business requirement or technical reason to store sec and ms. However, one pitfall to using smalldatetime is if one includes it as part of a unique key, or attempts to sort using the column, and doesn’t consider that multiple records inserted within approx minute will result in duplicate date/time values.
For example in a point of sale system, a key based on cash register ID and transaction smalldatetime may pass through QA and work in production without collision for weeks or months under normal circumstances, but then fail if a transaction is cancelled and a new transaction is immediately started. Also, I’ve seen circumstances where it was expected that transactions, perhaps line items on an invoice, would be sorted based on the order they were inserted, but the insert_date column was insufficient due to it not having sec or ms resolution.
Hello
how can i cut seconds from datetime, without rounding the minute?
i want the minutes to stay, but cut the seconds from date
Here is an example
declare @d datetime
select @d=’20010101 12:19:44′
select dateadd(minute,datediff(minute,0,@d),0)
Very Nice Article
I tested this query and i analyse that precision of smalldatetime is forwarded 1minute after 30second of actual time,Here is the script run together…
declare @test datetime2
set @test=GETDATE()
print @test
declare @test1 smalldatetime
set @test1=GETDATE()
print @test1
declare @test2 datetime
set @test2=GETDATE()
print @test2