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

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

CREATE OR REPLACE TABLE PUBLIC.date_table (
	date_col TIMESTAMP /*** MSC-WARNING - MSCEWI3060 - DEFAULT VALUE FOR SYSDATE IS CURRENT_TIMESTAMP. COLUMN WAS TRANSFORMED TO TIMESTAMP TO PRESERVE INFORMATION. ***/
);

INSERT INTO PUBLIC.date_table(
                              /*** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED ***/date_col) VALUES (DATE '2010-10-10');

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

Query

CREATE OR REPLACE TABLE PUBLIC.date_table (
	date_col date
);

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

Retrieving data from a Date column

Oracle

Query

SELECT date_col FROM date_table;

Result

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

Snowflake

Query

SELECT date_col FROM PUBLIC.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. MSCEWI3060: The default value for SYSDATE is CURRENT_TIMESTAMP. The column was transformed to TIMESTAMP to preserve information.

2. MSCEWI3061: The column was transformed from date to timestamp. Some operations may be affected.