Post reply

Error converting data type varchar to float.

  • August 27, 2010 at 1:41 pm

    #387680

    Hi

    My Table is:

    CREATE TABLE NewCDN

    (

    [Agent] char(30),

    [Target_Id] char(7),

    [Performance] varchar(6),

    [CountNum] char(4),

    [Availability] char(6),

    [Alias] char(55)

    )

    I'm Creating a View from this Table as:

    CREATE VIEW View_NewCDN

    AS

    SELECT Agent,

    Target_Id,

    CAST (CAST(Performance AS FLOAT) AS DECIMAL(7,3)),

    CountNum,

    Availability,

    Alias

    FROM NewCDN

    But I'm getting the folling error msg:

    Error converting data type varchar to float.

    This is because in Performance Column I have a comma instead of decimal.

    So my question is, how can i use the SET Statement in my query to replace comma with a decimal as:

    SET Performance = Replace(Performance,',','.')

    Thanks

  • August 27, 2010 at 1:47 pm

    #1213233

    How about

    CAST (Replace(Performance,',','.') AS DECIMAL(7,3))

    As a side note: Why did you try to cast it to float in between? Shouldn't be needed.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 27, 2010 at 1:54 pm

    #1213235

    Hi,

    I had similar problem in the past, so without looking into the data i used float which obviously didn't work. Then I figured out that I had comma instead of decimal in my Data.

    Thanks for the help.

  • August 27, 2010 at 2:14 pm

    #1213255

    Hi,

    Actually I want to use Agrregate function on this column like SUM(Performance) and this column also has '0' values in some rows. But instead of '0' it has '-' . So can you give me the syntax to replace both with agrregate on Performance?

    Thanks

  • August 27, 2010 at 2:49 pm

    #1213271

    How about providing table def, ready to use sample data and expected result? It would help a lot more than asking one question at a time...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 27, 2010 at 2:55 pm

    #1213275

    Hi,

    I got it right .... Ill post data if I have any other questions.

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply