Post reply

Error converting varchar to float help...

  • October 20, 2011 at 11:26 am

    #1397557

    Can you please post the DDL for the table?

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 20, 2011 at 11:32 am

    #1397566

    However, my first guess is that you are trying to round a varchar. Imagine trying to do this:

    SELECT ROUND('jared')

    I wouldn't do ROUND until your final calculation. However, for the requirements you may have to do it beforehand, and I understand that. Also, test the difference between CAST and CONVERT on performance is that is an issue.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 20, 2011 at 11:36 am

    #1397571

    The data for the elements all come from the HeatChemistry table. By DDL, do you mean a stored procdure? So far, I just have this as a normal query.

    Joe

  • October 20, 2011 at 11:38 am

    #1397572

    I will try CAST and see if that changes anything, thanks,

    Joe

  • October 20, 2011 at 11:43 am

    #1397579

    DDL = Data Definition Language.

    i.e. your create table scripts.

    Converting oxygen into carbon dioxide, since 1955.

  • October 20, 2011 at 11:45 am

    #1397583

    I'm sorry... Maybe you misunderstand. CAST vs CONVERT would only be for performance.

    Do this:

    SELECT ROUND(CAST(ColumnName AS FLOAT),2) FROM Table

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 20, 2011 at 11:51 am

    #1397587

    jared-709193 (10/20/2011)

    I'm sorry... Maybe you misunderstand. CAST vs CONVERT would only be for performance.

    Do this:

    SELECT ROUND(CAST(ColumnName AS FLOAT),2) FROM Table

    Thanks,

    Jared

    More specifically like this:

    USE [EAF]

    select

    WSHHeatNumberID,

    Grade,

    Round(CAST(Carbon AS FLOAT),2) AS C,

    Round(CAST(Nickel AS FLOAT),2) AS Ni,

    Round(CAST(Chromium AS FLOAT),2) AS Cr,

    Round(CAST(Molybdenum AS FLOAT),3) AS Mo,

    Round(CAST(Carbon AS FLOAT),2)+Round(CAST(Nickel AS FLOAT),2)+Round(CAST(Chromium AS FLOAT),2)+Round(CAST(Molybdenum AS FLOAT),3) AS CNCM

    From HeatChemistryFinal

    You see... You cannot ROUND a VARCHAR, which is your element's information. It really should be stored as a FLOAT. So, you have to convert/cast the VARCHAR before you can round it. You were fetting the error because of the ROUND, not because of the CONVERT.

    Lastly, you are just doing it to the returned data, not to tha actual data. Which means when you sum them up, you cannot reference the column alias.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 20, 2011 at 12:03 pm

    #1397592

    Wow, that was exactly what I was looking for. Thanks for all your help. That gave a different way to think about how I'm going to put my overall query together, but this one piece was giving me fits. Thanks Again,

    Joe

  • October 20, 2011 at 12:06 pm

    #1397593

    Awesome! FYI... I tested the CAST vs CONVERT. Use CONVERT for each one to increase performance over the CAST. So:

    ROUND(CONVERT(FLOAT,ColumnName),2)

    🙂

    Jared

    Jared
    CE - Microsoft

  • October 20, 2011 at 12:09 pm

    #1397595

    Sounds Good,

    Thanks,

    Joe 🙂

Viewing 10 posts - 1 through 11 (of 11 total)

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