Click here to Skip to main content
11,496,146 members (336 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
These are my tables
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

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 8-Mar-13 1:13am
Edited 8-Mar-13 1:15am
v2
Comments
Vardhan Desai at 8-Mar-13 6:47am
   
what value does Str([OPENPRICE],18,4) passes?
Error arise due to some invalid data .
now it solved
  Permalink  
what value does Str([OPENPRICE],18,4) passes?
  Permalink  
Comments
Hellboy.7604 at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 8 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100