Post reply

Please help converting decimal (9) to string without decimals

  • January 5, 2009 at 1:16 pm

    #155088

    Hi,

    I am trying to convert a decimal (9) to any type of string without decimal places. I am using the following code to attempt to do it.

    CAST(ROUND(dbo.invoice_line.qty_shipped, 0) AS varchar(40))

    The problem with this is that the value of dbo.invoice_.line_qty_shipped in the source is 5000 but when I convert it to a string it is changing it to 5000.000000000. I would like the converted value to be wihtout decimals.

    Thanks very much for any help!

  • January 5, 2009 at 1:30 pm

    #921403

    CAST(CAST(ROUND(dbo.invoice_line.qty_shipped, 0) as int) as varchar(40))

  • January 5, 2009 at 1:32 pm

    #921406

    That did it, thanks!

  • January 5, 2009 at 1:45 pm

    #921413

    Why cast to a varchar? If you're going to change to a different datatype, use something that doesn't allow a decimal like an int (bigint etc depending on your numbers). You'd still need the round to handle any issues that may entail...

    DECLARE @var1 DECIMAL(15,9), @var2 DECIMAL(15,9)

    SELECT @var1 = 5000.900000000, @var2 = 5000.400000000

    SELECT CAST(ROUND(@var1, 0) AS int) AS 'roundedup',

    CAST(ROUND(@var2, 0) AS int) AS 'roundeddown'

    Your front end should then be able to do an implicit conversion to a string.

    Hope that gets you what you are looking for.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • January 5, 2009 at 1:46 pm

    #921414

    heh, guess that's what happens when I forget to check previous posts after the phone rings...

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • January 5, 2009 at 4:07 pm

    #921484

    I think it's better to use floor

    CAST(ROUND(dbo.invoice_line.qty_shipped, 0) AS varchar(40))

    cast (floor(dbo.invoice_line.qty_shipped + .5) as varchar(40))

    It's simpler...

  • January 6, 2009 at 5:19 am

    #921675

    What's simple about any of that? And, like Luke said, why would you convert a perfectly good number to a VARCHAR?

    If you want to leave it as a number...

    CAST(yournumber AS INT)

    ... if you want to convert it to a VARCHAR...

    STR(yournumber,10)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 6, 2009 at 9:28 am

    #921895

    dude, I don't know why the fool wants to convert it to a varchar, it's part of the requirement on the post.

    He has his reasons, they aren't mine. Floor is still simpler. Stick to the requirements of the task and don't ask stupid questions.

  • January 6, 2009 at 7:01 pm

    #922288

    foxjazz (1/6/2009)

    dude, I don't know why the fool wants to convert it to a varchar, it's part of the requirement on the post.

    He has his reasons, they aren't mine. Floor is still simpler. Stick to the requirements of the task and don't ask stupid questions.

    Dude... don't call me dude! 😉 I'm thinking you're having a really bad day and you need to lighten up.

    And, ya just gotta ask questions so that people don't think this conversion should actually be done or that Float is the easy way to do this. :hehe:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 7, 2009 at 4:37 am

    #922423

    SELECT STR(Col1, 6, 0)

    N 56°04'39.16"
    E 12°55'05.25"

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

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