Issue with SQL format

Question

Hi,
Col0 below is one varchar column. I expect it to be shown in format like '####.##' by the code below but it is not OK. What to adjust?

format(cast(col0 as decimal(12,2)),'####.##')

Answer 1

Just to be clear.

You need to use "0000.00" as the format to have 0 fill.

Please see:
https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

DECLARE @tbl TABLE (col0 DECIMAL(18,4))  
INSERT INTO @tbl VALUES  
(123.45),  
(123.4),  
(123),  
(123.456)  
  
SELECT   
format(cast(col0 as decimal(12,2)),'####.##')  
,format(cast(col0 as decimal(12,2)),'####.00')  
,format(cast(col0 as decimal(12,2)),'0000.00')  
FROM @tbl  
  

Answer 2

See number below
102.9
103.91

I expect to have

0102.90
0103.91

Answer 3

Hi @Peter_1985 ,

Please have a try with below:

format(cast(col0 as decimal(12,2)),'0000.00')  

OR

right('0000'+ cast(cast(col0 as decimal(12,2)) as varchar(10)),7)  

If above is not working, please provide more sample data and expected output.

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.