SQL Server: implicit data type conversions chart

Question

As far as I can tell, according to this chart, an implicit conversion from VARBINARY to DATE should be OK. However, when I try the following code, SQL Server responds with "Implicit conversion from data type varbinary(max) to date is not allowed. Use the CONVERT function to run this query."

CREATE TABLE [dbo].[CONVERSION_TEST](
[mydate] [date] NULL
) ON [PRIMARY]

GO


declare @bin varbinary(MAX) = NULL
insert into conversion_test values (@bin)

I'm running this on SQL Server 2012 (version 11.0.3128).

Answer 1

Just found this connect post:

Microsoft Connect

Which has the following statement from Microsoft:

Posted by Microsoft on 05/03/2008 at 13:23 After some serious discussion, we believe that the current design is good especially given it's not an interesting scenario and we already support explicit conversion for the new date/time types.

On the other hand, we also think that it's not necessarily a good thing given we supported too many implicit conversions on existing types. Conversion to/from binary is a dangerous thing, and requiring explicit conversion makes sense. It's unfortunate that ALTER TABLE behavior is tied to implicit conversion, but you can always work around it by creating a new column, issue an update, and then drop old column if that's main scenario you worried.

So if no strong objection, we're going to do the followings: - Fixing the BOL to indicate that there's no implicit conversion support from binary/varbinary to new date/time types - Close this as 'By Design'

thanks

So it would appear that they decided to disallow this implicit conversion but not update that diagram.