BINARY_DOUBLE

Description

BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes. In a BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number). (Oracle Language Reference Binary_Double data type)

It is possible to specify floating-point numbers within the next limits:

• Maximum positive finite value = 1.79769313486231E+308

• Minimum positive finite value = 2.22507485850720E-308

Sample Source Patterns

Please, consider the following table and its inserts for the example below:

Binary Double in Create Table

Oracle

Query

CREATE TABLE binary_double_data_type_table
(
COL1 BINARY_DOUBLE
);

INSERT INTO binary_double_data_type_table VALUES(2.22507485850720E-308D);
INSERT INTO binary_double_data_type_table VALUES(1.79769313486231E+308D);
INSERT INTO binary_double_data_type_table VALUES('NaN');

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.binary_double_data_type_table (
COL1 FLOAT /*** MSC-WARNING - MSCEWI1036 - BINARY_DOUBLE NUMBER DATA TYPE CONVERTED TO FLOAT ***/);

INSERT INTO PUBLIC.binary_double_data_type_table VALUES(2.22507485850720E-308 /*** MSC-WARNING - MSCINF0010 - SUFFIX REMOVED FROM NUMERIC LITERAL ***/);

INSERT INTO PUBLIC.binary_double_data_type_table VALUES(1.79769313486231E+308 /*** MSC-WARNING - MSCINF0010 - SUFFIX REMOVED FROM NUMERIC LITERAL ***/);

INSERT INTO PUBLIC.binary_double_data_type_table VALUES('NaN');

'NaN' means Not a Number, this value is allowed by theBINARY_DOUBLE data type in Oracle and by theFLOATdata type in Snowflake.

BINARY_DOUBLE -> FLOAT

Since theBINARY_DOUBLEdata type is not supported by Snowflake it is being converted to FLOAT.

Oracle

Query

SELECT * FROM binary_double_data_type_table;

Result

col1                                                                                                                                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
                                                                                                                                                                                                                                                              0|
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
                                                                                                                                                                                                                                                            NaN|

Snowflake

Query

SELECT * FROM binary_double_data_type_table;

Result

col1                                                                                                                                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
                                                                                                                                                                                                                                                              0|
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
                                                                                                                                                                                                                                                            NaN|

Known Issues

1. The BINARY_DOUBLE data type is not supported by Snowflake

The BINARY_DOUBLE data type is converted to FLOAT since it is not supported by Snowflake.

Related EWIs

1. MSCEWI1036: Data type converted to another data type.

2. MSCINF0010: Suffixes in numeric literals were removed.