SQL SERVER – Puzzle – Inside Working of Datatype smalldatetime

In the recent past, I have been bringing few puzzles around using date and time functions. All these have brought some awesome feedbacks and you all have been sharing some awesome answers. Today’s question comes from an unusual place wherein one of a developer said he was getting unexpected results with working with datetime. So here is how the conversation went:

Dev: Hi Pinal.

Pinal: Yes, buddy. How can I be of help?

Dev: I have a problem working with datetime datatype.

Pinal: Oh is it? Tell me more about it.

Dev: I am trying to CAST a string coming from a front end app to SQL Server.

Pinal: Ok, where is the problem?

Dev: Every now and then, it gives different results than what I try to send via the wire.

Pinal: I am not able to understand. Can you send me an example to understand this better?

Dev: Sure, I will send via email.

After I received the actual code and email, I resumed the conversation.

Pinal: Hey.

Dev: Yes, Sir. Did you find the reason?

Pinal: I found the reason and I am going to blog about it tomorrow. It is a simplified version of the query you sent me. So you are about to find the answers there. The hint is: you are doing a convert to smalldatetime

Dev: Well, thanks. I think I will do my homework and try to answer it in your blog too.

This concept was interesting and I planned to share this experience with you folks like a small puzzle. So what are we talking?

Look at the below query and guess what would be the output:

SELECT
CAST('2015-01-01 12:45:29.755' AS SMALLDATETIME),
CAST('2015-01-01 12:45:35.755' AS SMALLDATETIME)

This is a simple conversion but the two values are different. Why is that? Do you know the reason? Use the comments section to explain this. I may send surprise gift to 2 of the winners.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime
Previous Post
SQL SERVER – DevOps for the DBA – Notes from the Field #091
Next Post
SQL SERVER – How to Remove All Characters From a String Using T-SQL?

Related Posts

47 Comments. Leave new

  • Benjamin Steinfeld
    July 31, 2015 7:54 am

    Smalldatetime rounds seconds off…

    Reply
  • Francis Lapeyre
    July 31, 2015 8:01 am

    SMALLDATETIME rounds the timestamp to the nearest minute; therefore, the results are:

    2015-01-01 12:45:00 and 2015-01-01 12:46:00.

    Reply
  • Maulesh Mevada
    July 31, 2015 10:18 am

    I didn’t get what error is facing by developer but,
    if you run in sql 2014 output will be,
    2015-01-01 12:46:00
    2015-01-01 12:45:00
    respectively,

    SMALLDATETIME does not contains seconds if seconds are provides in casting string it will round the date i.e. ignore a seconds and adjust minute

    Reply
  • 12:45:29.755 Rounded up 12:45 and 12:45:30.000 on wards rounded to 12:46

    Reply
  • The reason is becouse the accuracy of smalldatetime type is one minute. So that first answer is 45 and second is 46 minutes after noon

    Reply
  • small date time – discards the seconds by rounding up or rounding down any second greater than zero

    Reply
  • Hatim Dahodwala
    July 31, 2015 11:19 am

    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers to an accuracy of one three-hundredth of a second.The smalldatetime data type stores dates and times of day with less precision than datetime with accuracy to the minute. SQL Server stores smalldatetime values as two 2-byte integers. This is the reason why we see difference of a minute.
    Reference: https://www.microsoft.com/en-us/download/details.aspx?id=51958

    Reply
  • Shantanu Gupta
    July 31, 2015 11:27 am

    SELECT
    CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME), — 2015-01-01 12:45
    CAST(‘2015-01-01 12:45:35.755’ AS SMALLDATETIME) –2015-01-01 12:46

    Getting a result is easy by running this query however reason for rounding up and down is because of precision of smalldatetime which has a minimum precision of 1 minute. Since value in first cast statement is less than 29.998 seconds it is getting rounded down to nearest minute while in second example value is greater than 29.998 second it’s getting rounded up to 46 minute.

    2 years back I was stuck into another problem of rounding where ROUND function in sql server takes 3rd optional parameter which we generally aren’t aware of. I got a business requirement where i had to truncate data instead of rounding after two decimal places. While I was using round function with two parameters. So it was an interesting solution to discover.

    Reply
  • SMALLDATETIME has a precision of 1 minute – first one is rounded down (29 seconds), second one up (bigger than half a minute 35 sec)

    Reply
  • SELECT
    CAST(‘2015-01-01 12:45:29.755′ AS SMALLDATETIME), — 2015-01-01 12:45
    CAST(‘2015-01-01 12:45:35.755′ AS SMALLDATETIME) –2015-01-01 12:46

    Getting a result is easy by running this query however reason for rounding up and down is because of precision of smalldatetime which has a minimum precision of 1 minute. Since value in first cast statement is less than 29.998 seconds it is getting rounded down to nearest minute while in second example value is greater than 29.998 second it’s getting rounded up to 46 minute.

    2 years back I was stuck into another problem of rounding where ROUND function in sql server takes 3rd optional parameter which we generally aren’t aware of. I got a business requirement where i had to truncate data instead of rounding after two decimal places. While I was using round function with two parameters. So it was an interesting solution to discover.

    Reply
  • Hi Pinal,

    smalldatetime simply rounds to the 30 Seconds. That’s why if the seconds are more than 30 then it increments the minute part.

    Reply
  • Sir its reason CAST(‘2015-01-01 12:45:35.755’)—-> second column —-> time part wrote 12:45:35<———— this 35 is main reason if you write here <30 value then it gives same answer

    Reply
  • Sanjay Monpara
    July 31, 2015 11:52 am

    The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.

    I do not have better answer than this :-)
    https://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/

    Reply
  • https://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/ :) “The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.”.

    Reply
  • https://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/ :) “The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero. “

    Reply
  • Hi pinal,

    smalldatetime reveals the date with accuracy of minute. Smalldatetime values with 29.998 seconds or lower are rounded to the previous nearest minute and 29.999 seconds or higher are rounded to the next nearest minute.

    Reply
  • Smalldatetime will have only hours and minutes. While casting to it that will round off to nearest minute. I.e. Below 29 seconds will fall under previous minute and above will fall under upper minute

    Reply
  • Reason behind this is
    “The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.”

    Reply
  • I forgot to add about how rounding works..
    Values from 00 to 59 represents the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.

    Reply
  • SmallDateTime is only accurate to the nearest minute. In the first one, the value is rounded down to 12:45; the second value is rounded up to 12:46.

    Reply

Leave a Reply