# Changing Decimal #'s to Whole numbersRSS

## 9 replies

Last post May 04, 2015 04:15 AM by PatriceSc

• R.D.Inskeep

None

0 Points

2 Posts

### Changing Decimal #'s to Whole numbers

I have seen a couple of other threads that say that they can convert:

1.5 to 15

2.5 to 25

0.5 to 5

I need to know how I can convert

14.5 to 145

1.5 to 015

0.5 to 005

So that I can maintain the correct ratio between numbers, as 1.5 -= 15 and 0.5 = 5 would actually show in a list as

15

5

When it is assembled back on the other end (SAP) it would have 1.5 and 5.

Thanks!

• gimimex

Contributor

5450 Points

1123 Posts

### Re: Changing Decimal #'s to Whole numbers

Try something like this:

```declare @MyTable table
(MyValue decimal(10, 2));

insert into @MyTable values
(14.5), (1.5), (0.5);

select FLOOR(MyValue * 10) from @MyTable```

Hope this helps.

• limno

All-Star

122269 Points

9713 Posts

Moderator

### Re: Changing Decimal #'s to Whole numbers

You can work with number: 14.5*10=145,1.5*10=15 and 0.5*10=5.

The padding zero in front of a number is a format issue which you should work from your front end.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• Rechousa

Member

182 Points

57 Posts

### Re: Changing Decimal #'s to Whole numbers

Hi,

I guess you need a Pad Left function in SQL Server. I have already implemented that function in T-SQL:

```-- ====================================================================================
-- Author      : Pedro Martins (https://www.linkedin.com/in/rechousa)
-- Create date : 2014-08-21 14:53:04
-- Description : Pad left implementation in SQL Server
-- Project     : ---
-- Returns     : String
-- Remarks     : ---
-- ====================================================================================
(
@text AS VARCHAR(MAX),
@digits AS INTEGER,
@char AS VARCHAR(50)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN RIGHT(REPLICATE(@char, @digits) + @text, @digits);
END

GO
```

Now, just call it:

```DECLARE @n1 DECIMAL(5, 1) = 14.5;
DECLARE @n2 DECIMAL(5, 1) = 1.5;
DECLARE @n3 DECIMAL(5, 1) = 0.5;

SELECT CONVERT(VARCHAR(5), REPLACE(@n1,'.','')), dbo.PADL(CONVERT(VARCHAR(5), REPLACE(@n1,'.','')), 3, '0');
SELECT CONVERT(VARCHAR(5), REPLACE(@n2,'.','')), dbo.PADL(CONVERT(VARCHAR(5), REPLACE(@n2,'.','')), 3, '0');
SELECT CONVERT(VARCHAR(5), REPLACE(@n3,'.','')), dbo.PADL(CONVERT(VARCHAR(5), REPLACE(@n3,'.','')), 3, '0');
```

Hope this helps,

TSQL sqlserver function

Pedro Martins
Matosinhos
Portugal
• R.D.Inskeep

None

0 Points

2 Posts

### Re: Changing Decimal #'s to Whole numbers

That is the general idea, but I'm not really sure where I would use your PADL. Here is the query that I currently have and a couple of the results:

SELECT

P.PERSONID,
A.CODEID,
A2.NAME,
convert(varchar(10),A.EFFECTIVEDATE,112),
sum((A.AMOUNT)/3600  ) as SUM_Earned_Amount

And 3 of the records are:

PERSONID, CODEID, NAME, EFFDATE, SUM_EARNED_AMOUNT

123456    53    Vacation    20150131    0.76666666666
123457    1     PTO    20150131    3.38333333333

123458   1    PTO    20150121    14.76666666666

And we should write out in the last field: 00766 03833 and 14766 (or the first and last can round up to 00767 and 14767, respectively)

Appreciate the help

TSQL sqlserver function

• JoyceW

Contributor

4070 Points

1575 Posts

### Re: Changing Decimal #'s to Whole numbers

Can you try this:

SELECT

P.PERSONID, A.CODEID, A2.NAME, convert(varchar(10),A.EFFECTIVEDATE,112), right('00000' + cast(cast( sum((A.AMOUNT)/3600  ) * 10000 as int) as varchar(5)),5)    as SUM_Earned_Amount

TSQL sqlserver function

• limno

All-Star

122269 Points

9713 Posts

Moderator

### Re: Changing Decimal #'s to Whole numbers

The right way is to format your number result  in your front end. It is easy and clean.

TSQL sqlserver function

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• JoyceW

Contributor

4070 Points

1575 Posts

### Re: Changing Decimal #'s to Whole numbers

#### limno

The right way is to format your number result  in your front end. It is easy and clean.

No.  That is one way.  I would create a view with the query I posted and use the view.  That would be clean and easy too and you would only ever have to do it once.

There are many ways of doing things.

TSQL sqlserver function

• limno

All-Star

122269 Points

9713 Posts

Moderator

### Re: Changing Decimal #'s to Whole numbers

I would not recommend to format  a calculation result in a query within SQL Server( but we do sometimes for various reasons).

If you need the same query for different usages (different formats), you will need to manipulate string result again.

Keep the result in proper numeric state as designed and format it when you need it. That is the point.

TSQL sqlserver function

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• PatriceSc

All-Star

40430 Points

13157 Posts

### Re: Changing Decimal #'s to Whole numbers

Hi,

Seems you want something such as :

```   static string StringValue(decimal d)
{
return (d * 10).ToString("000");
}```

I assume that for some reason SAP expect integers, will turn back those strings into number and will divide by 10 to get the value? You really need the leading 0? What happens if you transmit something beyond 999 to SAP?

Finally note that if you have a leading 0, it is not a number by a string as 0 are not significant in numbers. I'm starting to think it might be a confusion with how numbers are shown on the SAP side? If numbers are shown with leading 0s on the SAP side you likely don't need to care about that. Just transmit the correct value and SAP will format this value when shown.

A SAP forum might be better as it seems some kind of special requirement SAP might have in how numbers should be transmitted...