cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL cast operator not working properly

my_community2
New Contributor III

please have a look at the attached screenshot

Three strings converted to float, each resulting in the same number.

22015683.000000000000000000 => 22015684

22015684.000000000000000000 => 22015684

22015685.000000000000000000 => 22015684

sql_cast

1 ACCEPTED SOLUTION

Accepted Solutions

MartinB
Contributor III

Hi @Maciej G​ ,

I guess, this has something to do with the data type FLOAT and its precision.

Floats are only an approximation with a given precision. Either you should consider using date type DOUBLE (double precision compared to FLOAT) - or, if you know upfront the number of digits before and after the decimal separator of the values to be expected use DECIMAL data type.

%%sql
WITH test_setup AS (
  SELECT '22015683.000000000000000000' AS `string`
  UNION ALL
  SELECT '22015684.000000000000000000' AS `string`
  UNION ALL
  SELECT '22015685.000000000000000000' AS `string`
)
SELECT
    `string`
  , cast(`string` AS float)            AS `float`
  , cast(`string` AS double)           AS `double`
  , cast(`string` AS decimal(26,18))   AS `decimal`
  , cast(`string` AS integer)          AS `integer`
FROM
  test_setup

image

View solution in original post

2 REPLIES 2

Kaniz
Community Manager
Community Manager

Hi @Maciej G​ ! My name is Kaniz, and I'm the technical moderator here. Great to meet you, and thanks for your question! Let's see if your peers in the community have an answer to your question first. Or else I will get back to you soon. Thanks.

MartinB
Contributor III

Hi @Maciej G​ ,

I guess, this has something to do with the data type FLOAT and its precision.

Floats are only an approximation with a given precision. Either you should consider using date type DOUBLE (double precision compared to FLOAT) - or, if you know upfront the number of digits before and after the decimal separator of the values to be expected use DECIMAL data type.

%%sql
WITH test_setup AS (
  SELECT '22015683.000000000000000000' AS `string`
  UNION ALL
  SELECT '22015684.000000000000000000' AS `string`
  UNION ALL
  SELECT '22015685.000000000000000000' AS `string`
)
SELECT
    `string`
  , cast(`string` AS float)            AS `float`
  , cast(`string` AS double)           AS `double`
  , cast(`string` AS decimal(26,18))   AS `decimal`
  , cast(`string` AS integer)          AS `integer`
FROM
  test_setup

image

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.