Post reply

Wierd convert problem

  • August 19, 2003 at 1:32 pm

    #81659

    We are converting a char to numeric (so we can do a sort) and the values being returned are wrong. Most of the time they are fine as in the first example.

    Here's the syntax:

    This one is fine:

    select convert(int,(convert(varbinary(10),rtrim('2003-189807')))) --returns 943274032

    select convert(int,(convert(varbinary(10),rtrim('2003-190047')))) --returns 959459380

    This one is wacky:

    select convert(int,(convert(varbinary(20),rtrim('2003-189807')))) --returns 959983671

    select convert(int,(convert(varbinary(20),rtrim('2003-190047')))) --returns 808465463

    Why would the bottom one line return a value that's lower (then the first example) , when the number we are passing it is higher?

    Any help or suggesions GREATLY appreciated.

  • August 19, 2003 at 1:39 pm

    #470465

    I have no idea why you are converting to varbinary...seems odd. Try:

    

    SELECT CAST(REPLACE(RTRIM(MyField), '-','') AS INT)

  • August 20, 2003 at 9:44 am

    #470620

    This is duplicated in two forums. The problem is that the string is 11 characters. Convert Varbinary 10 cuts off the last character.

    Guarddata-

  • August 20, 2003 at 10:54 am

    #470632

    Jpipes, Thanks for your reply. Your answer works. However, We may have an occasional alpha character in the mix. Which makes it break, any solution?

    Thanks again

  • August 20, 2003 at 10:58 am

    #470633

    Well, technically, there is no reason to do any conversion whatsoever. As long as the format of the character data is consistent (meaning, you are comparing XXXX-XXXXXXX to XXXXXXX and not XX-XXXX to XXXX-XXXXXXX), use character data. You are just slowing things down by trying to sort after converting to a numeric value. Remember, isolate the part of the string that varies, then sort by that string.

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

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