Data Types
Snowflake supports most basic SQL data types (with some restrictions) for use in columns, local variables, expressions, parameters, and any other appropriate/suitable locations.
Exact and approximate numerics
BIGINT
BIGINT
Snowflake's BIGINT is an alias for NUMBER(38,0)
DECIMAL
DECIMAL
Snowflake's DECIMAL is synonymous with NUMBER
DECFLOAT
VARCHAR
DECFLOAT data type is not supported in Snowflake. VARCHAR is used instead.
The default is the maximum allowed length (16,777,216).
DOUBLE PRECISION
DOUBLE PRECISION
Snowflake's DOUBLE PRECISION is synonymous with FLOAT
FLOAT
FLOAT
This data type behaves equally on both systems.
Precision 7-15 digits, float (1-24)
Storage 4 - 8 bytes, float (25-53)
INT
INT
Snowflake's INT is an alias for NUMBER(38,0)
INTEGER
INTEGER
Snowflake's INTEGER is synonymous with INT.
REAL
REAL
Snowflake's REAL is synonymous with FLOAT
SMALLINT
SMALLINT
This data type behaves equally
NUMERIC
NUMERIC
Snowflake's NUMERIC is synonymous with NUMBER
Character strings
VARCHAR
VARCHAR
Snowflake VARCHAR holds Unicode UTF-8 characters. If no length is specified, the default is the maximum allowed length (16,777,216).
CHAR
CHAR
Snowflake uses this data type as synonymous with VARCHAR, except that if the length is not specified, CHAR(1) is the default.
CHAR VARYING
CHAR VARYING
Snowflake uses this data type as synonymous with VARCHAR.
CHARACTER
CHARACTER
Snowflake uses this data type as synonymous with VARCHAR.
CHARACTER VARYING
CHARACTER VARYING
Snowflake uses this data type as synonymous with VARCHAR.
Character Large Object
CHAR LARGE OBJECT
VARCHAR
CHAR LARGE OBJECT data type is not supported in Snowflake. VARCHAR is used instead.
The default is the maximum allowed length (16,777,216).
CHARACTER LARGE OBJECT
VARCHAR
CHARACTER LARGE OBJECT data type is not supported in Snowflake. VARCHAR is used instead.
The default is the maximum allowed length (16,777,216).
CLOB, CLOB(SIZE)
VARCHAR
CLOB data type is not supported in Snowflake. VARCHAR is used instead.
The default is the maximum allowed length (16,777,216).
National Character String
NATIONAL CHARACTER,
NATIONAL CHAR
NCHAR
If Characters or Octets is present in the data size, it will be commented since it is not supported in Snowflake.
NATIONAL CHARACTER VARYING,
NATIONAL CHAR VARYING,
NCHAR VARYING
NCHAR VARYING
If Characters or Octets is present in the data size, it will be commented since it is not supported in Snowflake.
National Character Large Object
NATIONAL CHARACTER LARGE OBJECT
VARCHAR
Binary Large Object strings
BLOB
BINARY
BLOB data type is not supported in Snowflake. BINARY is used instead.
The default is the maximum allowed length (8,388,608 bytes).
BINARY LARGE OBJECT, BINARY LARGE OBJECT(SIZE)
BINARY
BINARY LARGE OBJECT data type is not supported in Snowflake. BINARY is used instead.
The default is the maximum allowed length (8,388,608 bytes).
Boolean
BOOLEAN
BOOLEAN
BOOLEAN can have TRUE or FALSE values.
Date Time
DATE
DATE
DATE accepts dates in the most common forms (YYYY-MM-DD
, DD-MON-YYYY
, etc.)
TIME
TIME
Storing times in the form of HH:MI:SS
. Time precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.
TIMESTAMP
TIMESTAMP
Timestamp precision can range from 0 (seconds) to 9 (nanoseconds).
TIMESTAMP WITH TIME ZONE
TIMESTAMP_TZ
TIMESTAMP_TZ internally stores UTC time together with an associated time zone offset.
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP_NTZ
TIMESTAMP_NTZ internally stores “wallclock” time with a specified precision.
Time with or without time zone
The data type Time does not support the clause with or without out Time Zone in Snowflake
ANSI SQL Input Code
CREATE TABLE TABLE_TEST (
COLNAME1 TIME (9) WITH TIME ZONE,
COLNAME2 TIME WITHOUT TIME ZONE
);
Snowflake Output Code
CREATE TABLE PUBLIC.TABLE_TEST (
COLNAME1 TIME (9)
-- ** MSC-ERROR - MSCEWI1096 - TIME ZONE NOT SUPPORTED FOR TIME DATA TYPE **
-- WITH TIME ZONE
,
COLNAME2 TIME
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. WITHOUT TIME ZONE **
-- WITHOUT TIME ZONE
);
Interval
INTERVAL
VARCHAR(21)
There's not a direct equivalent in Snowflake, to learn more about this transformation, please refer to Interval section
Related EWIs
MSCEWI1002: Removed next statement, not applicable in Snowflake.
MSCEWI1096: TIME ZONE not supported for time data type.
Last updated
Was this helpful?