CODEWITHSUNDEEP
t-sql
SidC
SidC

Reputation: 3203

How to Format a String with Leading Zeroes to Decimal in TSQL?

I'm trying to parse a string such that the amount field is formatted with 2 decimal places. The original data provides 2 decimal place accuracy. However, the amount field in the data file is 16 characters long with leading zeroes. For example:

0000000000407981

should convert to 4079.81. I've tried the following in my select statement:

format((substring([Column 0],51,16)/100), '.00') as CheckAmount,

This produces an amount with 2 decimal places but rounds to the nearest whole dollar. I'm using SQL Server 2016.

How do I modify my statement to ensure the CheckAmount is formatted with 2 decimal point accuracy and contains an accurate value?

Update: I attempted to convert to integer as follows:

format(convert(int, (substring([Column 0],51,16)/100.0)), '.00') as CheckAmount,

Unfortunately, I receive an error stating:

Arithmetic overflow error converting varchar to data type numeric.

How should I remedy this?

Upvotes: 0

Views: 704

Answers (2)

SidC
SidC

Reputation: 3203

I was able to solve this issue with the following code snippet:

CAST((CAST(substring([Column 0],51,16) as int)/100.0) as decimal(18,2)) as CheckAmount,

Hope this helps anyone who might need to accomplish a similar task.

Upvotes: 0

Sander
Sander

Reputation: 4042

The divide by 100 does an integer division. You do not get decimal numbers. Dividing by 100.0 will.

declare @num nvarchar(20) = '0000000000407981';

select convert(bigint, @num)/100.0;

Fiddle

Upvotes: 1

Related Questions