Danny Ocean (3/15/2013)

---

kapil_kk (3/15/2013)

---

Danny Ocean (3/15/2013)

---

Really a nice and good question.

one more thing apart from this question. if you set ARITHABORT OFF and then execute below code, it will give output as "*" due to insufficient char length. and if you set char length more than 7, it will give "0.111111".

It denotes that we need to provide an insufficient length whenever we convert values in char or varchar.

SET ARITHABORT OFF

select isnull ( convert (char(3),1.0/9) ,'*')

Vinay, I tried with ARITHABORT OFF but still I am getting same error "Arithmetic overflow error converting numeric to data type varchar."

set arithabort off

select ISNULL(CONVERT(char(3),1.0/9),'*')

Kapil, you need to SET ANSI_WARNINGS OFF. Look at below two examples. Example 1 execute successfully but example 2 give an error.

---- Example 1

SET ANSI_WARNINGS OFF

SET ARITHABORT OFF

GO

select isnull ( convert (char(3),1.0/9) ,'*') ,isnull ( convert (char(8),1.0/9) ,'*')

GO

SET ARITHABORT ON

SET ANSI_WARNINGS ON

---- Example 2

SET ANSI_WARNINGS ON

GO

select isnull ( convert (char(3),1.0/9) ,'*') ,isnull ( convert (char(8),1.0/9) ,'*')

GO