CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
toby
toby

Reputation: 59

Snowflake cast Int to Decimal

I'm working on Migrating SQL Server data to Snowflake and trying to convert Int to Decimal. But the response is not what I'm expecting.

In SQL If you run this query Select 1.0000 or Select Cast(1 as Decimal(12,4)) the result will 1.000. If I do the same in Snowflake the result is 1. Is there a way to fix it?

Thanks

Upvotes: 1

Views: 3132

Answers (3)

Rudra Kumar
Rudra Kumar

Reputation: 1

SELECT TO_CHAR(CAST(1 AS DECIMAL(12,4)))

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Select Cast(1 as Decimal(12,4)) the result will 1.000. If I do the same in Snowflake the result is 1. Is there a way to fix it?

Representation of the number does not matter. It is most likely a client tool that shows it without decimal places.

When EXPLICIT cast is involved the data type is set as requested and it could be easily confirmed with DESCRIBE RESULT:

SELECT CAST(1 AS DECIMAL(12,4)) AS col;

DESCRIBE RESULT LAST_QUERY_ID();
-- name     type           kind      null?
-- col      NUMBER(12,4)   COLUMN    Y

DECIMAL and NUMBER are synonyms in Snowflake.

Upvotes: 1

demircioglu
demircioglu

Reputation: 3465

You can use

select to_number(1,12,4)

For more info

Upvotes: 1

Related Questions