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)
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.
Dear pinal m waiting for your response
The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.
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
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
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.