SSC-FDM-OR0042

Date Type Transformed To Timestamp Has A Different Behavior

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Date type is being transformed to either Date or Timestamp type depending on flag --disableDateAsTimestamp, because Date type in Snowflake has a different behavior than Oracle.

Key Differences

	Oracle DATE	Snowflake DATE
Functionality	Stores date and time information	Stores only date information (year, month, day)
Internal Storage	Binary number representing seconds since epoch	Compact format optimized for dates
Use Cases	General-purpose date and time storage	Scenarios where only date information is needed
Advantages	Supports both date and time	More efficient storage for dates
Limitations	Can't store date and time components separately.	Doesn't store time information

Example Code

Input Code (Oracle):

IN -> Oracle_01.sql

CREATE TABLE "PUBLIC"."TABLE1"
(
    "CREATED_DATE" DATE,
    "UPDATED_DATE" DATE
);

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE "PUBLIC"."TABLE1"
    (
        "CREATED_DATE" TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        "UPDATED_DATE" 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"}}'
    ;

OUT -> Oracle_01b.sql

-- Additional Params: --disableDateAsTimestamp
CREATE OR REPLACE TABLE "PUBLIC"."TABLE1"
    (
        "CREATED_DATE" DATE /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
        "UPDATED_DATE" DATE /*** 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"}}'
    ;

Recommendations

• No end-user actions are required.

• If you need more support, you can email us at [email protected]