Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    19

    Unanswered: Convert INT and LTRIM not working?

    Hello,

    I have a column in a table with some numerical data. Its a varchar column.

    I am trying to sum it up, so convert to int. For whatever reason the data has spaces in front, so I am trying to trim as well. Using the following code:

    Code:
    SELECT LTRIM(SUM(CAST(REGHR AS INT))) as Hours FROM TimeClock WHERE JOBID=" & job & ""
    But it still returns the following:
    Code:
    Conversion failed when converting the varchar value ' 8.00' to data type int.
    I cant figure out how to trim this up... any advice?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rtown View Post
    Code:
    SELECT LTRIM(SUM(CAST(REGHR AS INT))) ...
    you're casting to integer first, then taking the sum, then trimming the result of the sum

    what you actually want to do is trim first, then cast, then sum

    get it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    19
    Well, I got it to trim. But it still will not convert to int... how can "8.00" not be an int?

    It converts to Money just fine... haha!
    Code:
    SELECT SUM(CONVERT(MONEY, LTRIM(REGHR))) as Hours FROM TimeClock WHERE JOBID=" & job & ""
    Sure, money is fine!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The decimal point makes the string ' 8.00' a float, which does not survive the CHAR to INT conversion.

    The MONEY type allows decimals, so that conversion works fine.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    declare @i varchar(max)=' 8.00'
    select cast(@i as money)
    select cast(cast(@i as money)as int)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Sep 2010
    Posts
    153

    ltrim, rtrim or trim doesnt work here

    You can use left instead of ltrim, right instead of rtrim.

    example

    select left('good morning', 4) will give you an output as "good".

    or select right('good morning', 7);

  7. #7
    Join Date
    Sep 2010
    Posts
    153

    converting float values to integer values

    We can't directly convert float values to integer values as far as i know
    so what we can do is that we can divide float values by 1.00 etc.

    for example if the value is '8.00', we can divide the number by 1.00

    ex - select cast(('8.00'/1.00) as int) will give us an integer value.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunny_007 View Post
    You can use left instead of ltrim, right instead of rtrim.
    please demonstrate how you would use LEFT/RIGHT when the number of spaces to be trimmed varies, from 0 through to several

    that's a pretty crappy solution in this example

    Quote Originally Posted by sunny_007 View Post
    ex - select cast(('8.00'/1.00) as int) will give us an integer value.
    are you sure? did you test this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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