How do I convert cents retained as integers to dollars as decimals?

Question

I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals.

Example

A value is kept as 1925, so I need to be able to return 19.25.

This is what I had been doing in MySQL

format(price/ 100, 2) as amount

and when summing the value, I use

cast(SUM(price) / 100 AS DECIMAL(12, 2)) as total_amount

These seem to work fine, but when moving over to Redshift, I am not sure if I am using the right function.

Current attemtp

In Redshift, I try with

price :: DECIMAL(12, 2) as amount

and then

SUM(price)/ 100 :: DECIMAL(12, 2) as total_amount

In both instances, I get 4 decimal points in my results? Is using wrapping these functions with trunc(,2) a good way to solve this?

Answer 1

You can use ROUND or TO_CHAR

Using ROUND

ROUND(price :: DECIMAL(12, 2), 2) as amount
-- or
ROUND(price :: DECIMAL, 2) as amount
-- or
ROUND(SUM(price)/ 100 :: DECIMAL(12, 2), 2) as total_amount
-- or
ROUND(SUM(price)/ 100 :: DECIMAL, 2) as total_amount

USING TO_CHAR

TO_CHAR(price :: DECIMAL(12, 2), 'FM99999999.99') as amount
-- or
TO_CHAR(price :: DECIMAL, 'FM99999999.99') as amount
-- or
TO_CHAR(SUM(price)/ 100 :: DECIMAL(12, 2), 'FM99999999.99') as total_amount
-- or
TO_CHAR(SUM(price)/ 100 :: DECIMAL, 'FM99999999.99') as total_amount

Format strings are explained in more detail here.