Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Unanswered: DataType converting, char to float

    I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
    I've tried this query:
    SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
    And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

    Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: DataType converting, char to float

    --Find bad values
    select YourCol
    from YourTable
    where isnumeric(YourCol)=0

    /*
    Then choose, what numeric type do you want.
    Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
    Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
    Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
    rounded up to 1+4n bytes.
    Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
    */

    --if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

    --Sending test values would be your benefit.
    Last edited by ispaleny; 01-09-03 at 06:59.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

    The following post discusses this problem:

    post

  4. #4
    Join Date
    Jan 2003
    Posts
    5
    Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
    This is the code:

    Dim InsertQuery As String
    Set oConn = New Connection

    Set oRec = New Recordset
    oRec.Open InsertQuery, oConn

    is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

    -jr

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    You need to post an actual insert statement and the data types as defined by the table.

  6. #6
    Join Date
    Jan 2003
    Posts
    5
    InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

    Is a test-query I use

    And the table is defined as follows:
    Part - char - size 11 - Do not allow nulls
    Serial - char - size 8 - Do not allow nulls
    [Time] - datetime - Do not allow nulls
    S0 - decimal - Precision: 2 - Do not allow nulls

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Your post has 2 main problems
    1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
    2. You use VB Now() function, which is setting-specific. Use getdate() on server.

    Code:
    create table testNum(
     Part  char (11) not null
    ,Serial char(8) not null
    ,[Time]  datetime not null
    ,S0  decimal(15,2) not null
    )
    GO
    INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
    --faster
    INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.

  9. #9
    Join Date
    Jan 2003
    Posts
    5
    Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.

  10. #10
    Join Date
    Jan 2003
    Posts
    5
    Just one more thing..
    When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    This will happen in enterprise manager - run a query in query analyzer and you will see them.

Posting Permissions

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