SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision

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)

About these ads

9 thoughts on “SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision

  1. 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′

  2. Sir,
    I’ve used this datatype so many times but i’ve not noticed the issue yet. Thanks for posting this issue…..

  3. 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.

  4. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – SmallDateTime and Precision – A Continuous Confusion « SQL Server Journey with SQL Authority

  6. Hello
    how can i cut seconds from datetime, without rounding the minute?
    i want the minutes to stay, but cut the seconds from date

  7. 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

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s