SELECT RIGHT('0000'+ CONVERT(VARCHAR, xxxx ),4)

Replacing the 'xxxx' with your query above.

Solved

Posted on 2014-04-28

Hello:

Below is a T-SQL field that I programmed called "ytdhours".

As you can see, it needs to be a total of six digits--four to the left of the decimal place and two to the right.

But, I need leading zeroes to dynamically appear to the left of the decimal. For example, the result that was derived from running this was "429.75". I need the result to be "0429.75". Or, if the result had been "29.75", I would need it to actually be "0029.75".

How can I accomplish this?

Thanks!

TBSupport

CAST(SUM(MED.dbo.UPR30301.MTDHOURS_1 / CAST(100 AS DECIMAL(18, 2)))

+ SUM(MED.dbo.UPR30301.MTDHOURS_2 / CAST(100 AS DECIMAL(18, 2))) + SUM(MED.dbo.UPR30301.MTDHOURS_3 / CAST(100 AS DECIMAL(18, 2)))

+ SUM(MED.dbo.UPR30301.MTDHOURS_4 / CAST(100 AS DECIMAL(18, 2))) + SUM(MED.dbo.UPR30301.MTDHOURS_5 / CAST(100 AS DECIMAL(18, 2)))

+ SUM(MED.dbo.UPR30301.MTDHOURS_6 / CAST(100 AS DECIMAL(18, 2))) + SUM(MED.dbo.UPR30301.MTDHOURS_7 / CAST(100 AS DECIMAL(18, 2)))

+ SUM(MED.dbo.UPR30301.MTDHOURS_8 / CAST(100 AS DECIMAL(18, 2))) + SUM(MED.dbo.UPR30301.MTDHOURS_9 / CAST(100 AS DECIMAL(18, 2)))

+ SUM(MED.dbo.UPR30301.MTDHOURS_10 / CAST(100 AS DECIMAL(18, 2))) + SUM(MED.dbo.UPR30301.MTDHOURS_11 / CAST(100 AS DECIMAL(18, 2)))

+ SUM(MED.dbo.UPR30301.MTDHOURS_12 / CAST(100 AS DECIMAL(18, 2))) AS decimal(6, 2)) AS ytdhours

Below is a T-SQL field that I programmed called "ytdhours".

As you can see, it needs to be a total of six digits--four to the left of the decimal place and two to the right.

But, I need leading zeroes to dynamically appear to the left of the decimal. For example, the result that was derived from running this was "429.75". I need the result to be "0429.75". Or, if the result had been "29.75", I would need it to actually be "0029.75".

How can I accomplish this?

Thanks!

TBSupport

CAST(SUM(MED.dbo.UPR30301.

+ SUM(MED.dbo.UPR30301.MTDHO

+ SUM(MED.dbo.UPR30301.MTDHO

+ SUM(MED.dbo.UPR30301.MTDHO

+ SUM(MED.dbo.UPR30301.MTDHO

+ SUM(MED.dbo.UPR30301.MTDHO

+ SUM(MED.dbo.UPR30301.MTDHO

3 Comments

Comment Utility

Try the following:

SELECT RIGHT('0000'+ CONVERT(VARCHAR, xxxx ),4)

Replacing the 'xxxx' with your query above.

SELECT RIGHT('0000'+ CONVERT(VARCHAR, xxxx ),4)

Replacing the 'xxxx' with your query above.

Comment Utility

>I need the result to be "0429.75".

Then you need to display it as a character value, as no numeric data types support leading zeros. Copy-paste the below T-SQL into your SSMS, execute it to verify it's what you need, then modify to meet your needs.

Then you need to display it as a character value, as no numeric data types support leading zeros. Copy-paste the below T-SQL into your SSMS, execute it to verify it's what you need, then modify to meet your needs.

```
Declare @val numeric(7,2) = 429.75
SELECT RIGHT('0000000' + CAST(@val as varchar(7)), 7)
```

Comment Utility

Thank you, for both responses!

@JEaston: That worked! I had to use 7 zeroes and a "7" at the end. I'm not sure why that worked instead of "6". But, it did.

Thank you!

TBSupport

@JEaston: That worked! I had to use 7 zeroes and a "7" at the end. I'm not sure why that worked instead of "6". But, it did.

Thank you!

TBSupport

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**6** Experts available now in Live!