[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

T-SQL: Leading Zeroes

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
0
TBSupport
Asked:
TBSupport
1 Solution
 
John EastonDirectorCommented:
Try the following:

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

Replacing the 'xxxx' with your query above.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.

Declare @val numeric(7,2) = 429.75

SELECT RIGHT('0000000' + CAST(@val as varchar(7)), 7) 

Open in new window

0
 
TBSupportAuthor Commented:
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now