Invalid TimeStamp Literal when defaulting a timestamp column

Database
Enthusiast

Invalid TimeStamp Literal when defaulting a timestamp column

This fails with a syntax error of CREATE TABLE Failed. 3706:  Syntax error: Invalid TimeStamp Literal. :

CREATE SET TABLE TEST,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COL1_CODE CHAR(4) NOT NULL,

      ROW_END_DTTM TIMESTAMP(6) DEFAULT TIMESTAMP '9999-12-31 23:59:59'

      )

UNIQUE PRIMARY INDEX TEST_UPI ( COL1_CODE );

But this next statement succeeds by changing the time hour portion of the default timestamp from 23 to 18.

CREATE SET TABLE TEST,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COL1_CODE CHAR(4) NOT NULL,

      ROW_END_DTTM TIMESTAMP(6) DEFAULT TIMESTAMP '9999-12-31 18:59:59'

      )

Any ideas?

1 REPLY
Senior Apprentice

Re: Invalid TimeStamp Literal when defaulting a timestamp column

You're located at time zone -05:00 :-)

Teradata tries to adjust the timestamp to this time zone which results in '10000-01-01 04:23:59+00:00', which is obviously out of the valid range.

Try TIMESTAMP '9999-12-31 23:59:59+00:00' instead.

Dieter