Solved

T-SQL:  Leading Zeroes

Posted on 2014-04-28
3
225 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
ID: 40027356
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
ID: 40027366
>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
ID: 40027380
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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