Error converting data type varchar to numeric.

Last Post 20 Sep 2007 09:02 AM by hyoung. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
srinath003
New Member
New Member

--
20 Sep 2007 06:13 AM
Hi Everybody,

Can anybody suggest me in converting varchar to numeric.

when i am trying to convert it to numeric it is trowing error
Error converting data type varchar to numeric.

the following is the query..


select
c.EMP_NM sales_professional,
prob,
year_mth_nbr,
(sum(month_vol)) mthvol,
(sum(Prob_vol)) probvol

from


(
select
SALES_TERR_ID ,
PRBLY prob,
substring(convert(varchar(10),projd_ship_dt, 112),1,6) as year_mth_nbr,
sum(convert( decimal(19,2),VALUE_ANLYS )) month_vol,
sum(convert( decimal(19,2),VALUE_ANLYS ))*prbly prob_Vol
from fxsp_opportunity
where projd_ship_dt is not null
group by sales_terr_id,prbly,projd_ship_dt
) a,
employee_assignment b,
employee c
where a.sales_terr_id = b.sales_terr_id
and b.emp_nbr = c.emp_nbr
and ((year_mth_nbr = substring(convert(varchar(10),getdate(), 112),1,6) + 1) or
(year_mth_nbr = substring(convert(varchar(10),getdate(), 112),1,6) + 2)or
(year_mth_nbr = substring(convert(varchar(10),getdate(), 112),1,6) + 3)or
(year_mth_nbr = substring(convert(varchar(10),getdate(), 112),1,6) + 4))

group by
c.EMP_NM,
prob,
year_mth_nbr
hyoung
New Member
New Member

--
20 Sep 2007 09:02 AM
Convert( varchar(24), getdate(), 114 ) is the format I use for changing getdate.

The following may be of help to all:

select Convert( varchar(24), getdate(), 100 )-- Oct 3 2006 9:42AM
select Convert( varchar(24), getdate(), 101 )-- 10/03/2006
select Convert( varchar(24), getdate(), 102 )-- 2006.10.03
select Convert( varchar(24), getdate(), 103 )-- 03/10/2006
select Convert( varchar(24), getdate(), 104 )-- 03.10.2006
select Convert( varchar(24), getdate(), 105 )-- 03-10-2006
select Convert( varchar(24), getdate(), 106 )-- 03 Oct 2006
select Convert( varchar(24), getdate(), 107 )-- Oct 03, 2006
select Convert( varchar(24), getdate(), 108 )-- 09:49:44
select Convert( varchar(24), getdate(), 109 )-- Oct 3 2006 9:50:27:867
select Convert( varchar(24), getdate(), 110 )-- 10-03-2006
select Convert( varchar(24), getdate(), 111 )-- 2006/10/03
select Convert( varchar(24), getdate(), 112 )-- 20061003
select Convert( varchar(24), getdate(), 113 )-- 03 Oct 2006 09:51:04:867
select Convert( varchar(24), getdate(), 114 )-- 09:51:04:867
SQLUSA
New Member
New Member

--
21 Sep 2007 01:40 AM
You can filter your data with ISNUMERIC prior to conversion.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandprix
You are not authorized to post a reply.

Acceptable Use Policy