Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/1240473169095528449
Incorporated two relevant tags and removed an irrelevant one. Modified formatting. Made some rewordings.
Source Link
MDCCL
MDCCL
  • 8.5k
  • 3
  • 31
  • 62

How do I convert cents storedretained as intergerintegers to dollars as decimal in Redshiftdecimals?

I have centcent values stored, retained as an integerintegers, that I need to convert to dollarsdollars and show them as a decimaldecimals.

Example:Example

A value is storedkept as 19251925, so I need to be able to return 19.2519.25.

This is what I had been doing in MySQL

format(price/ 100, 2) as amount

format(price/ 100, 2) as amount

and when summing the value, I use -

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

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 -.

Currently using -Current attemtp

price :: DECIMAL(12In Redshift, 2) as amountI try with

price :: DECIMAL(12, 2) as amount

SUM(price)/ 100 :: DECIMAL(12, 2) as total_amountand 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)trunc(,2) a good way to solve this?

How do I convert cents stored as interger to dollars as decimal in Redshift

I have cent values stored as an integer that I need to convert to dollars and show as a decimal.

Example: value is stored as 1925, 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 -

Currently using -

price :: DECIMAL(12, 2) as amount

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?

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

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?

Source Link
D3AD_PIX3L
D3AD_PIX3L
  • 41
  • 1
  • 2

How do I convert cents stored as interger to dollars as decimal in Redshift

I have cent values stored as an integer that I need to convert to dollars and show as a decimal.

Example: value is stored as 1925, 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 -

Currently using -

price :: DECIMAL(12, 2) as amount

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?