Create a table with a date column that has a default

Database
Enthusiast

Create a table with a date column that has a default

Hi all,

No amount of google searching or manual reading has led me to the solution to this problem:

I would like to create a table that has a column that is a date.  I want said column to have a default value.

CREATE TABLE T1
(
COL1 INTEGER NOT NULL DEFAULT 0,
COL2 DATE format 'YYYY-MM-DD' NOT NULL DEFAULT to_date('2000-01-01','YYYY-MM-DD')
)
UNIQUE PRIMARY INDEX (COL1 );

I've tried a few dozen permutations of this, and I keep getting errors like:

 *** Failure 3706 Syntax error: expected something between the 'DEFAULT' key word and the 'to_date' keyword.

If I remove the to_date() function, I get:

 *** Failure 3630 Default value incompatible with type of column COL2. Statement# 1, Info =0

Any help getting past this frustrating error would be appreciated.  At least there'll be something for the next guy to google.

Regards,

Matt.

4 REPLIES
Enthusiast

Re: Create a table with a date column that has a default

I hope this helps...

CREATE SET TABLE T1
(
COL1 INTEGER NOT NULL DEFAULT 0,
COL2 DATE NOT NULL FORMAT 'YYYY-MM-DD' DEFAULT DATE '2000-01-01')
)
UNIQUE PRIMARY INDEX ( T1 );

Enthusiast

Re: Create a table with a date column that has a default

Works!  Thank you. 

Still amazed that neither of the cast syntaxes are needed for this and that the word DATE is needed to tell a date column that that default is also a date.

CREATE SET TABLE T1
(
COL1 INTEGER NOT NULL DEFAULT 0,
COL2 DATE NOT NULL FORMAT 'YYYY-MM-DD' DEFAULT DATE '2000-01-01')
UNIQUE PRIMARY INDEX ( COL1 );

Teradata Employee

Re: Create a table with a date column that has a default

TO_DATE is oracle specific function not supported in Teradata.

Senior Apprentice

Re: Create a table with a date column that has a default

TO_DATE exists in Teradata since TD14 (2012), before it was available as a C-UDF.