2011-09-21

TSQL - Comma number formatting


select
      SUBSTRING
      (
            CONVERT( varchar, CAST( 1234567 as money ), 1 )
            , 1
            , charindex
            (
                  '.'
                  , CONVERT(varchar(20), CAST( 1234567 as money ), 1 )
                  , 2
            ) - 1
      )
;
By

1 comment:

  1. Anonymous14:27

    Use ROUND before you apply this if you do not intend to truncate... this is the formula using round before doing this:


    SUBSTRING
    (
    CONVERT( varchar, CAST( ROUND( 123456, 0 ) as money ), 1 )
    , 1
    , charindex
    (
    '.'
    , CONVERT(varchar(20), CAST( ROUND( 123456, 0 ) as money ), 1 )
    , 2
    ) - 1
    )

    ReplyDelete

Subscribe to: Post Comments (Atom)