Over the years, every time I have worked with Date and time data types, I have learnt something new. There are always tons of tight corners where a number of exceptions happen. Though these are known, for someone who is new, this would take them by surprise and your design decisions can have a larger impact on the output that you are likely to get. These can cause a considerable business impact and compliance if we get stuff wrong. Let us see a puzzle about Date Data Types.
In this puzzle, let me show you some of the default behavior that SQL Server does when working with CAST. Below is an example wherein we take a simple conversion to a DATE.
As you can see, SQL Server went about doing the rounding and it gives the date as 2017-01-13. This seems to be something basic. The puzzle was not around this output.
Task 1
What do you think is the output of the below query? Is it going to be:
SELECT CAST ('2017-1-13 23:59:59.999' AS SMALLDATETIME)Output:
- 2017-01-13 23:59:00
- 2017-01-13 00:00:00
- 2017-01-14 00:00:00
- Error
What do you think is the answer? Why do you think this is the answer?
Task 2
If you got the Task 1 correct, I think you know a lot about working with datetimes. Having said that, what would be the output for the below:
SELECT CAST('2017-1-13 23:59:59.999999999' AS SMALLDATETIME)Output:
- 2017-01-13 23:59:00
- 2017-01-13 00:00:00
- 2017-01-14 00:00:00
- Error
Did Task 1 help you? How did you guess it? Why do you think you got this output in task 2?
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
All about the precision for SMALLDATETIME, which is only precise to the minute, rounding seconds to almost “unexpected” values.
Still, a good teaser.
I am agree with Andre, Both query generate different data type precision of SMALLDATETIME is 16 so if you provide any date that have more than 16 digits then SQL Serve throw an error of “Conversion failed when converting character string to smalldatetime data type.”.
So output of of the second task will be an error and output of task 1 is “2017-01-14 00:00:00” because as per value of second section SQL Server convert the datetime to nearest datetime.
Task 1
What do you think is the output of the below query? Is it going to be:
SELECT CAST ('2017-1-13 23:59:59.999' AS SMALLDATETIME)Output is: 2017-01-14 00:00:00
Justification: as per MSDN documentation, any values that are 29.998 seconds or less (in a smalldatetime “ss” part) are rounded down to the nearest minute & values of 29.999 seconds or more are rounded up to the nearest minute
Task 2
If you got the Task 1 correct, I think you know a lot about working with datetimes. Having said that, what would be the output for the below:
SELECT CAST('2017-1-13 23:59:59.999999999' AS SMALLDATETIME)Output: Error
Justification: This one throws error because smalldatetime can no hold more than 3 digits in the precision part of “ss” by design. Same is the case for DateTime as well. However DateTime2 is capable of storing more than 3 (upto 7)