Cast to datetime is not working on sql server 2016

Question

I am getting Arithmetic overflow error while executing following query on sql server 2016.

select cast(2958463.99999999 as datetime)

The same query works fine on sql server 2014.

Edit 1- select cast(2958463.9999999 as datetime) works on sql server, the only difference is that it used 7 digits after decimal point instead of 8 as originally posted.

This error is caused by a change in Microsoft SQL Server 2016's handling of datetime types. — Yogesh Sharma, Oct 25 '17 at 12:16
You sure there isn't anything more in the value? I just run it on couple versions and everywhere works in the same fashion. — , Oct 25 '17 at 12:16
The query works fine on mine 2016 instance. The output I get is `9999-12-31 23:59:59.997`. — Giorgos Betsos, Oct 25 '17 at 12:18
even 'select cast(2958463.9999999 as datetime)' with 7 digit after decimal is working on sql server 2016 — mohit uprim, Oct 25 '17 at 12:21
which management studio are you using? You should download SQL Mangement studio 17 then it will work for you. — SqlKindaGuy, Oct 25 '17 at 13:07
@mohituprim As your not clear about which instance or management studio you use, i have tried different solutions which you can see in my answer. Hope this gives you an idea. — SqlKindaGuy, Oct 25 '17 at 14:03

score 1 · Accepted Answer · answered Oct 25 '17 at 12:18

1

Try the following in 2016 (notice the last digit)

select cast(2958463.99999996 as datetime)

answered Oct 25 '17 at 12:18

John Cappelletti

79,615
7
44
66

Why then it would work without this change on SQL2016s I have at hand? – Oct 25 '17 at 12:19
This is working but i am not sure about the reason..could you please explain? thanks – mohit uprim Oct 25 '17 at 12:24
@mohituprim I suspect a matter of precision. Take a peek at this answer – John Cappelletti Oct 25 '17 at 12:27
https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond – John Cappelletti Oct 25 '17 at 12:30

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You need to have the newest SQL Server Management studio available.

This is both on instances with Server version 2014 (12.0.4439.1) and 2016(13.0.4435.0).

NOTE:

SQL Server Management Studio 2017 only works with 7 digits on instance 2016(13.0.1601.5)

NOTE2:

SQL Server Management Studio 2014 only works with 7 digits on instance 2016(13.0.4435.0)

SQL Server Management Studio 2014 works with all digits on instance 2016(13.0.1601.5)

SQL Server Management Studio 2014 works will all digits on instance 2014(12.0.4439.1)

You can download the newest version here SQL Server Management Studio 2017

Tested on SQL Server instance 2016(13.0.4435.0)

Tested on SQL Server instance 2014(12.0.4439.1)

Tested on SQL Server Instance 2016(13.0.1601.5)

Cast to datetime is not working on sql server 2016

2 Answers2