SSC-FDM-OR0042

Date Type Transformed To Timestamp Has A Different Behavior

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 BEHAVIOR DIFFERENCE IN SNOWFLAKE. ***/,
        "UPDATED_DATE" DATE /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A BEHAVIOR DIFFERENCE 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 [e-mail]