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 DATESnowflake 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

Last updated