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 rounds seconds off…
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.
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
12:45:29.755 Rounded up 12:45 and 12:45:30.000 on wards rounded to 12:46
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
small date time – discards the seconds by rounding up or rounding down any second greater than zero
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
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.
SMALLDATETIME has a precision of 1 minute – first one is rounded down (29 seconds), second one up (bigger than half a minute 35 sec)
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.
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.
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
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.
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
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.”
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.
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.
Hi,
As per my knowledge, Date time rounded to nearest minute after 30 sec on wards.
It display respectively.
2015-01-01 12:45:00 2015-01-01 12:46:00
From the result, we can understand that It round off the seconds.
1)2015-01-01 12:45:29.755 — 2015-01-01 12:45:00 ( Sub 30 seconds )
2)2015-01-01 12:45:35.755 — 2015-01-01 12:46:00 ( Add 30 seconds )
Thanks,
Vaibhav Shukla
Hi Pinal
This is because converting to smalldatetime data type rounds of the minute by discarding the seconds. So output will be “2015-01-01 12:45:00” and “2015-01-01 12:46:00” respectively.
E:g –
SELECT
CAST(‘2015-01-01 00:01:29.999’ AS SMALLDATETIME), CAST(‘2015-01-01 00:01:29.998’ AS SMALLDATETIME)
Thanks
Sudhev