Post reply

t-sql 2012 return number with 2 leading zeroes

  • May 5, 2015 at 10:29 am

    #296563

    In a t-sql 2012 select statement, I have a query that looks like the following:

    SELECT CAST(ROUND(SUM([ABSCNT]), 1) AS NUMERIC(24,1)) from table1. The field called [ABSCNT] is declared as a double. I would like to know how to return a number like 009.99 from the query. I would basically like to have the following:

    1. 2 leading zeroes (basically I want 3 numbers displayed before the decimal point)

    2. the number before the decimal point to always display even if the value is 0, and

    3. and 2 digits after the decimal point.

    Thus can you show me the sql that I can use to meet my goal?

  • May 5, 2015 at 10:48 am

    #1795686

    Perhaps something like this:

    declare @MyValue float = 0.2;

    select right('000' + ltrim(str(@MyValue,10,2)),6);

  • May 5, 2015 at 5:10 pm

    #1795781

    You can also use the FORMAT function introduced in 2012 for this:

    DECLARE @val DECIMAL(24,1) = 0.2

    SELECT FORMAT(@val, '000.00')

  • May 5, 2015 at 5:52 pm

    #1795788

    Ohhhhh, be REAL careful about using STR for formatting. Its slow and can be the source of rounding problems that you might not expect.

    http://www.sqlservercentral.com/articles/T-SQL/71565/

    I'll also state that, unless you're writing to fixed field files, you should probably avoid formatting in T-SQL.

    --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)

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

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