SQL cast operator not working properly

my_community2 — Fri, 11 Feb 2022 18:56:05 GMT

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

Re: SQL cast operator not working properly

MartinB — Mon, 14 Feb 2022 21:17:41 GMT

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

topic SQL cast operator not working properly in Data Engineering

SQL cast operator not working properly

Re: SQL cast operator not working properly