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)),'####.##')
Issue with SQL format
Could you please provide some sample data and expected output after doing format?
Best regards,
Melissa
Could you please validate all the answers so far and provide any update?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Best regards,
Melissa
Just to be clear.
You need to use "0000.00" as the format to have 0 fill.
Please see:
https://docs.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
See number below
102.9
103.91
I expect to have
0102.90
0103.91
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.
18 people are following this question.
