All Forums Database
boxerdog 6 posts Joined 06/10
11 Sep 2012
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.

 

Qaisar Kiani 337 posts Joined 11/05
12 Sep 2012
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 );
boxerdog 6 posts Joined 06/10
24 Sep 2012

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 );
23 Jul 2016

TO_DATE is oracle specific function not supported in Teradata.

dnoeth 4628 posts Joined 11/04
23 Jul 2016

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

Dieter

You must sign in to leave a comment.