Solved

T-SQL:  Leading Zeroes

Posted on 2014-04-28
3
220 Views
Last Modified: 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
0
Comment
Question by:TBSupport
3 Comments
 
LVL 10

Accepted Solution

by:
JEaston earned 500 total points
Comment Utility
Try the following:

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

Replacing the 'xxxx' with your query above.
0
 
LVL 65

Expert Comment

by:Jim Horn
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.

Declare @val numeric(7,2) = 429.75

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

Open in new window

0
 
LVL 1

Author Comment

by:TBSupport
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now