I want to place a decimal between the first and second digit of the interest rate and keep 2 trailing after the decimal. How do I go about doing this?
x
SELECT TOP 10 l.PARENTACCOUNT AS [Account Number]
, l.interestrate AS [Interest Rate]
FROM dbo.LOAN l
This is my current result:
Account Number Interest Rate
0000000107 9900
0000000107 11900
0000002000 5750
0000002460 10300
0000002652 9900
0000003850 0
0000004942 7510
0000004942 4990
0000004942 5000
0000006652 6790
This is my desired result:
Account Number Interest Rate
0000000107 9.90
0000000107 1.19
0000002000 5.75
0000002460 1.03
0000002652 9.90
0000003850 0.00
0000004942 7.51
0000004942 4.99
0000004942 5.00
0000006652 6.79
EDIT: I ended up using this to get the correct interest rate
ISNULL(convert(decimal(4, 2), stuff(convert(varchar(255), interestrate), 2, 0, '.')), 0)
Advertisement
Answer
Strange format; you cannot handle interest rates less than 1%.
You can do:
select convert(decimal(4, 2), stuff(convert(varchar(255), interestrate), 2, 0, '.'))
This converts the value to a number, adds the decimal place, and converts to a decimal.
Here is a db<>fiddle.