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)

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

  • Smalldatetime datat type has an accuracy upto minutes. Also, 29.998 or lower seconds are rounded down to the neareset minutes and 29.999 and above seconds are rounded up to the nearest minute value. This is the reason why CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME) rounds to the “2015-01-01 12:45:00” as second part 29.755 is lower than 29.998 and
    CAST(‘2015-01-01 12:45:30.755’ AS SMALLDATETIME) rounds to “2015-01-01 12:46:00” as second’s part 30.755 is greater than 29.999.

    Reply
  • Mubashar Anwer
    August 4, 2015 5:18 pm

    Dear pinal m waiting for your response

    Reply
  • 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
  • Sathiyamoorthy
    June 2, 2016 9:22 pm

    Since seconds has 60 strikes, its neutral value is 30. I believe

    Round up will happen when the value is above 29
    Round down will happen when the value below 30

    Reply
  • Sathiyamoorthy
    June 2, 2016 9:23 pm

    Since seconds has 60 strikes, its neutral value is 30. I believe

    Its minute round up will happen when the second’s value is above 29
    Its minute round down will happen when the second’s value below 30

    Reply
  • NISHIKANT GOUTAM
    April 27, 2017 10:22 pm

    Smalldatetime has precision of minute , hence its round it to nearest minute. If seconds are grater than or equal to 30 it round up to next minutes value , if its less than 30 seconds will round down.

    Reply

Leave a Reply

Menu