DATE Data Type

Description

Oracle's date data type stores both date and time information, however Snowflake's date data type only stores date information. (Oracle SQL Language Reference Date Data Type)

The default transformation for Oracle DATE is to Snowflake TIMESTAMP. You can add the disableDateAsTimestamp flag (SnowConvert Command Line Interface) or disable the Transform Date as Timestamp setting (SnowConvert desktop application) in order to transform the DATE type to TIMESTAMP. Keep in mind that Snowflake DATE only stores date information and Oracle stores date and time information, if you want to avoid losing information you should transform DATE to TIMESTAMP.

DATE

Sample Source Patterns

Date in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE date_table
(
	date_col date
);

INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');

Snowflake without --disableDateAsTimestamp flag or with "Transform Date as Timestamp" setting enabled

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE date_table
	(
		date_col TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
	;

	INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');

Snowflake with --disableDateAsTimestamp flag or with "Transform Date as Timestamp" setting disabled

Query

CREATE OR REPLACE TABLE date_table
	(
		date_col date
	)
	COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
	;

	INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');

Retrieving data from a Date column

Oracle

Query

IN -> Oracle_02.sql

SELECT date_col FROM date_table;

Result

|DATE_COL               |
|-----------------------|
|2010-10-10 00:00:00.000|

Snowflake

Query

OUT -> Oracle_02.sql

SELECT date_col FROM
date_table;

Result with default settings

|DATE_COL               |
|-----------------------|
|2010-10-10 00:00:00.000|

Result with disableDateAsTimestamp flag

|DATE_COL  |
|----------|
|2010-10-10|

Known Issues

1. Input and output format may differ between languages

In Snowflake, DATE input and output formats depend on the DATE_INPUT_FORMAT and DATE_OUTPUT_FORMAT session variables. Insertions may fail because the DATE_INPUT_FORMAT enforces the user to use a specific format when a date is added by text. You can modify those variables using the following syntax.

ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-DD-MM' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';

Related EWIs

1. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior