Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 t-sql 2012 return number with 2 leading zeroes

Author  Topic 

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-05-05 : 12:27:58
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?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-05 : 13:05:35
wrap in RIGHT('00' + CAST(<original expression> as VARCHAR(26)), 26):

SELECT RIGHT('00' + CAST(CAST(ROUND(SUM([ABSCNT]), 1) AS NUMERIC(24,1) AS VARCHAR(26), 26) from table1

Gerald Britton, MCSA
Toronto PASS Chapter
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-05-05 : 13:58:14
Thanks!
Go to Top of Page
   

- Advertisement -