Good question..and I have seen this behavior earlier as well.
As per BOL, "*" is returned when we try to convert to char and varchar and
Error message when try on convert to nchar, nvarchar...
Any specific reason why Microsoft has implemented this way?
I would have implemented error in all cases either it be char,varchar,nvarchar or nchar to keep it simple and easy to developer 😉
SQL Server DBA | MCSE SQL Server 2012/2014