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.

SQL SERVER – Precision of SMALLDATETIME – A 1 Minute Precision smalldatetimep

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)

,
Previous Post
SQLAuthority News – Monthly Roundup of Best SQL Posts
Next Post
SQL SERVER – Stored Procedure and Transactions

Related Posts

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’

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

    Reply
  • Eric Russell
    June 7, 2010 7:50 pm

    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.

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

    Reply
    • Here is an example

      declare @d datetime
      select @d=’20010101 12:19:44′
      select dateadd(minute,datediff(minute,0,@d),0)

      Reply
  • 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

    Reply

Leave a Reply

Menu