I thought of sharing this with you , which i experienced today during work. I was getting error, popping a message which was
saying ......
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.
On execution of this script
declare @varchar varchar(128) ='12345678.910'
select cast(@varchar as bigint)
Was not able to get any clue how this is happening??????
According to my opinion, this may be because only one level of conversion is happening in SQL Server. What I meant by saying one level of conversion is that, there is no intermediate conversion. Like in this case, “@varchar” should have been converted into FLOAT implicitly and then into BIGINT.
Thus during explicit conversion of character type to numeric type, it checks whether character is of target data type or not, if yes then converts to target type. Like here variable “@varchar” is of FLOAT type not INT data type, hence this can be converted into FLOAT but not in INT or BIGINT.
Thus on Execution of
declare @varchar varchar(128) ='12345678910'
select cast(round(@varchar,0) as bigint)
we get
output as
--------------------
12345678910
(1 row(s)
affected)
Please comment and share your knowledge on this .
