Post reply

Formatting Aggregate Currency Fields

  • June 6, 2012 at 8:46 am

    #275207

    Does anyone know how to format an aggregate numeric field into a currently format and then have decimal places in it.

    Example:

    select sum(t.total_spend)

    should look like:

    $45,004.12

    Thanks

  • June 6, 2012 at 10:09 am

    #1497734

    Normally, you'd do this kind of formatting in the front end, not in tsql. But this might help

    declare @x decimal(12,2)

    declare @z varchar(50)

    declare @y int

    set @x = 61.13

    set @z = cast(@x as varchar(45))

    set @y = datalength(@z)

    select @z = case when @y > 9

    then '$' + stuff(stuff(@z,(@y - 8),0,','),(@y - 4),0,',')

    when @y > 6

    then '$' + stuff(@z,(@y - 5),0,',')

    else

    '$' + @z

    end

    select @z

    Ugly code and I'm sure there are better ways to do this, but it's all I had time to play with now.

    And then again, I might be wrong ...
    David Webb

  • June 6, 2012 at 10:26 am

    #1497741

    Thanks for the reply. I'm like a newbie to SQL, so I have no idea how to translate this to a simple query statement :{

  • June 6, 2012 at 10:27 am

    #1497742

    Forgot to mention... this is for oracle and the front end I'm using PL/SQL developer....

  • June 6, 2012 at 10:34 am

    #1497748

    rhonda.klass (6/6/2012)

    Forgot to mention... this is for oracle and the front end I'm using PL/SQL developer....

    You'd be better off asking this on an Oracle forum, then, since this is really a MS SQL Server forum.

  • June 6, 2012 at 10:36 am

    #1497751

    Thanks... do you happenn to have a website for an oracle forum....

  • June 6, 2012 at 10:39 am

    #1497754

    Nope.

Viewing 7 posts - 1 through 7 (of 7 total)

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