Solved

T-SQL:  Leading Zeroes

Posted on 2014-04-28
3
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Accepted Solution

by:
John Easton 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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