Post reply

Converting money field

  • June 8, 2005 at 2:23 pm

    #67101

    In the sql below, column pol_fac_at is defined as dataype money. For a value of $5113.40, it is being returned in the select as 5113.4000

    I want it to be returned as 00000511340.

    How can I get that? Any help is greatly appreciated.

    thanks!

    SELECT top 10

    VIEW_RepRatio_NewPolicy_Agent.pol_fac_at

    FROM

    VIEW_RepRatio_NewPolicy_Agent

  • June 8, 2005 at 2:43 pm

    #563994

    How do you know where the decimal starts?

    Also I feel I must mention that this presentational stuff should be made at the client.

    But if you insist on doing it on the server :

    Declare @x as money

    set @x = 5113.4

    select @x

    Select RIGHT('00000000000' + REPLACE(CAST(@X as varchar(9)), '.', ''), 11)

    --00000511340

    Please retest this with your data because I'm not sure it'll always work.

  • June 9, 2005 at 1:28 am

    #564083

    One might be inclined to think, that this is presentational stuff done best at the client. Anyway, piggy-backing on Remi, here's another method

    SELECT REPLACE(STR(@x*100,11), ' ', '0')

    for the lazycoder.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 9, 2005 at 6:36 am

    #564165

    Quoting myself :

    "Also I feel I must mention that this presentational stuff should be made at the client."

    .

  • June 9, 2005 at 3:11 pm

    #564407

    Thanks Frank and Remi! I appreciate the help.

  • June 9, 2005 at 3:11 pm

    #564406

    HTH.

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

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