Click here to Skip to main content
15,904,153 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
These are my tables
SQL
CREATE TABLE [dbo].[dailyRate](
[SYMBOL] [varchar](50) NULL,
[SERIES] [varchar](50) NULL,
[OPENPRICE] [varchar](50) NULL,
[HIGHPRICE] [varchar](50) NULL,
[LOWPRICE] [varchar](50) NULL,
[CLOSEPRICE] [varchar](50) NULL,
[LASTPRICE] [varchar](50) NULL,
[PREVCLOSE] [varchar](50) NULL,
[TOTTRDQTY] [varchar](50) NULL,
[TOTTRDVAL] [varchar](50) NULL,
[TIMESTAMPDAY] [varchar](50) NULL,
[TOTALTRADES] [varchar](50) NULL,
[ISIN] [varchar](50) NULL
)

CREATE TABLE [dbo].[cmpDailyRate](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[SYMBOL] [varchar](50) NULL,
[SERIES] [varchar](50) NULL,
[OPENPRICE] [decimal](18, 4) NULL,
[HIGHPRICE] [decimal](18, 4) NULL,
[LOWPRICE] [decimal](18, 4) NULL,
[CLOSEPRICE] [decimal](18, 4) NULL,
[LASTPRICE] [decimal](18, 4) NULL,
[PREVCLOSE] [decimal](18, 4) NULL,
[TOTTRDQTY] [bigint] NULL,
[TOTTRDVAL] [decimal](18, 4) NULL,
[TIMESTAMPDAY] [smalldatetime] NULL,
[TOTALTRADES] [bigint] NULL,
[ISIN] [varchar](50) NULL,
[M_Avg] [decimal](18, 4) NULL
) 


this is my insert query to fetch data from table to another with casting

SQL
INSERT into [Stock].[dbo].[cmpDailyRate]
SELECT [SYMBOL],[SERIES],Str([OPENPRICE], 18,4),Str([HIGHPRICE],18,4),
Str([LOWPRICE],18,4),Str([CLOSEPRICE],18,4),Str([LASTPRICE],18,4),Str([PREVCLOSE],18,4),convert(bigint,[TOTTRDQTY]),Str([TOTTRDVAL],18,4),
convert(date, [TIMESTAMPDAY], 105),convert(bigint,[TOTALTRADES]),[ISIN],null
FROM [Stock].[dbo].[DailyRate]


above query run perfectly in sql2005
but giving error in sql2008(above query run also in sql2008 when installed ,error arise in last few days )
error :Error cannot convert varchar to float
what to do?
Posted
Updated 8-Mar-13 0:15am
v2
Comments
MalwareTrojan 8-Mar-13 6:47am    
what value does Str([OPENPRICE],18,4) passes?

Error arise due to some invalid data .
now it solved
 
Share this answer
 
what value does Str([OPENPRICE],18,4) passes?
 
Share this answer
 
Comments
Er.HimanshuShah 12-Mar-13 17:45pm    
OPENPRICE column has float value. and this function "Str([OPENPRICE],18,4)" gives result in character format. but i insert the result in decimal(18,4) data type column. and it work.
it need to be cast.but without casting it works.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900