CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformteradataddlsnowflake-schema
abdoulsn
abdoulsn

Reputation: 1159

Default DATE Snowflake: invalid identifier 'DATE'

I'm migrating data from teradata to Snowflake I've converted the DDL of Teradata to Snowflake (checked it in roboquery).

When I run the converted ddl in Snowflake, I get an error on default value of DATE.

CREATE OR REPLACE TABLE XXX.YYYY 
(
    ID NUMBER(8) NOT NULL,
    DERN TIMESTAMP(0) ,
    OBSDATE TIMESTAMP(0)  NOT NULL,
    DATFULL DATE DEFAULT DATE
);

I got this error:

Unexpected error running Liquibase: SQL compilation error: error line 5 at position 25 invalid identifier 'DATE' [Failed SQL: (904)?

Why do I get this error - Snowflake uses DATE?

Is there any risque using current_date (updated post after first answer of @Marcel).

Thanks

Upvotes: 0

Views: 3888

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

Based on documentation Terradata - DEFAULT:

This form...
DEFAULT DATE quotestring

Inserts …
the date value specified by quotestring as the default for the column DATE.

DEFAULT CURRENT_DATE

the current system date

The date literals(DATE 'some_date') is supported by Snowflake:

CREATE OR REPLACE TABLE YYYY (
 ID NUMBER(8) NOT NULL,
 DERN TIMESTAMP(0) ,
 OBSDATE TIMESTAMP(0)  NOT NULL,
 DATFULL DATE DEFAULT DATE '1900-01-01'   -- any specific date here
);

Date and Time Constants:

Constants (also known as literals) refers to fixed data values. Snowflake supports using string constants to specify fixed date, time, or timestamp values. String constants must always be enclosed between delimiter characters.

date '2010-09-14'
time '10:03:56'
timestamp '2009-09-15 10:59:43'

To sum up, depending on requirements: DEFAULT DATE 'YYYY-MM-DD' or if it has to be a current date DEFAULT CURRENT_DATE.

Upvotes: 1

Marcel
Marcel

Reputation: 2622

I assume you want to use CURRENT_DATE as default value?

Then this is the code:

CREATE OR REPLACE TABLE XXX.YYYY (
 ID NUMBER(8) NOT NULL,
 DERN TIMESTAMP(0) ,
 OBSDATE TIMESTAMP(0)  NOT NULL,
 DATFULL DATE DEFAULT CURRENT_DATE);

Upvotes: 1

Related Questions