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
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
According to the query it gives two different result as we are using SMALLDATETIME for casting. In SQL server datatype SMALLDATETIME values with 29.998 seconds or lower are rounded down to the nearest minute.
So the first one will be rounded to ‘2015-01-01 12:45:00’ and second will be rounded to ‘2015-01-01 12:46:00’ as it has more than 30 seconds.
Thanks
From specification of smalldatetime
….
Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute……
The reason is because there is difference in the seconds. The one which has more than 30 seconds converts it into the next minute and the one with less than 30 seconds converts it into the same minute.(We can relate it to round off function)
cast in smalldatetime converts datetime into 24Hr format. And it will always have seconds as 0. Also it will always convert any second more then 29 will be rounded off by adding a minute in time and any second less then 29 will be rounded off by subtracting a minute from time.
A SMALLDATETIME value is stored as two two-byte integers. The first integer represents the date, and the second integer represents the time.The time does not reflect the number of seconds, and if seconds are included in the value, they are rounded 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.
Because the precision of the SMALLDATETIME datatype is 1 minute. Seconds are discarded by rounding.
I mean, rounded up or down, depending on the value of the seconds
The reason these values are different is due to the seconds element for smalldatetime. Values that are 29.998 seconds or less are rounded down to the nearest minute while values of 29.999 seconds or more are rounded up to the nearest minute.
Sir it’s working like a rounding function and rounding it, like 12:45:29.755 will be rounded to 12.45 as ms part of time is 30.
Hey… In smalldatetime data type, “second” values that are 29.99 seconds or less are rounded down (floor) to the nearest minute, where values of 29.99 seconds or more are rounded up to the nearest minute (ceiling). In this case, 12.45.29, the second 29 is actually 29.00000 then rounded down to 12:45, where 12:45:35, 35 seconds is greater than 29.99 so it is rounded up to 12:46. Cheers.
2015-01-01 12:45:00 2015-01-01 12:46:00
That is the output i got when i ran the SQL statement. I think if the seconds greater than 30 then it is going to the next minute when rounding.
smalldatetime datatype returns date/time value with accuracy to a minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.
Looks to me like a rounding difference. SmallDateTime doesn’t go to millisecond, so SQL Server has to choose which minute to display. Looks to me like it does simple rounding to the minute, but it ignores the millisecond component and just rounds based on the full seconds.
The reason why is because if the seconds are more than 30 than the time is rounded up in minutes and if the seconds are less than 30 than the minutes are rounded down. This is shown on msdn website: https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-2017
smalldatetime rounds up or down based on the seconds. So 2015-01-01 12:45:29.755 would round down, and 2015-01-01 12:45:35.755 will round up to 12:46. Similarly, 2015-01-01 12:45:30.755 would round up to 12:46 as well.
As the msdn say, the format use a 24 hour time with 00 as seconds always and 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 rounding to mm:ss (30 seconds and above rounded to next minute). Hence we are getting different results.
The time part in SMALLDATETIME contains seconds as always zero (:00) and without fractional seconds. If we store/assign any fractional seconds to smalldatetime value, it discards the seconds by rounding up or rounding down.
If seconds are =30 then rounds up
In this way the results vary whenever we try to cast datetime values with seconds to smalldatetime…
SELECT
CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME), –2015-01-01 12:45:00
CAST(‘2015-01-01 12:45:35.755’ AS SMALLDATETIME), –2015-01-01 12:46:00
CAST(‘2015-01-01 12:45:30.000’ AS SMALLDATETIME), –2015-01-01 12:46:00
CAST(‘2015-01-01 12:45:30.755’ AS SMALLDATETIME), –2015-01-01 12:46:00
CAST(‘2015-08-03 23:59:59.000’ AS SMALLDATETIME) — 2015-08-04 00:00:00
Regards,
Chandrika