Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Question Convert text to float in SQL Statement

    Hi,

    Can you guys help me out?
    I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

    I do get the answer but its not the correct figure. My SQL statement is as follow:

    SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

    Please help.

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: Convert text to float in SQL Statement

    Try avoiding using flaot, pretty inaccurate.

    Use decimal or numeric data types instead.
    Shadow to Light

  3. #3
    Join Date
    Feb 2004
    Posts
    4

    Re: Convert text to float in SQL Statement

    Originally posted by Crespo-n00b
    Try avoiding using flaot, pretty inaccurate.

    Use decimal or numeric data types instead.
    I've tried it and it still gives me a wrong answer.
    I should have 45299 + 7832.5 = 53131.5, but I kept getting
    13310.

    Any more ideas?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,397
    A Miracle ????
    Code:
    use pubs
    
    create table answerint
    (
    QTY1 varchar(20),
    QTY2 varchar(20)
    )
    
    insert into answerint select '45299','7832.5'
    
    SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint
    
    drop table answerint
    Works for me though !!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Crespo and a mriacle in the same post...holy cow!

    Where have you been hiding out?

    And Yes float is quirky....

    But I would add

    USE Northwind

    CREATE TABLE answerint
    (
    QTY1 varchar(20),
    QTY2 varchar(20)
    )

    INSERT INTO answerint
    SELECT '45299','7832.5' UNION ALL
    SELECT 'BRETT', 'KAISER'

    SELECT SUM(
    (Cast(Qty1 as float))
    + (Cast(Qty2 as float))
    ) as intAnswer FROM answerint
    WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

    DROP TABLE answerint
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Brett,

    I have not been hiding anywhere. Just busy with work and life you know....

    Amethystium.
    Shadow to Light

  7. #7
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Crespo-n00b
    Brett,

    I have not been hiding anywhere. Just busy with work and life you know....

    Amethystium.
    Thanks for the replies.
    I've tried it all out and they work for single select but not when a
    "SUM" is used.

    But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

    Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •