Creating a Timestamp(6) with Timezone column with Default value

Database
Enthusiast

Creating a Timestamp(6) with Timezone column with Default value

Hello,

Thanks in advance for the knowledge you guys are sharing.

I am trying to create a table with TIMESTAMP(6) with TIME ZONE and default that column to ‘'9999/12/31 00:00:000000 +00:00’

create tablet1(
record_end_dtm TIMESTAMP(6) with TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ ' NOT NULL DEFAULT '9999-12-31 11:59:00.000000+00.00' )

I am getting the below error. 3630: Default Value incompatible with type of column Record_end_time

I know I am not giving the format right in the above statement but could not find any documentation. Appreciate any help.

Thank You,
MGajsk.
3 REPLIES
Enthusiast

Re: Creating a Timestamp(6) with Timezone column with Default value

You need to tell dbc that the character constant is really a timestamp.

create table t1(
record_end_dtm TIMESTAMP(6) with TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ ' NOT NULL DEFAULT Timestamp '9999-12-31 11:59:00' )
Enthusiast

Re: Creating a Timestamp(6) with Timezone column with Default value

Thank you Jimm!! That helped.

Our Dwh has decided to use Timestamp(6) with TimeZone datatype for all Date columns to capture the live transactions times.

I was also wondering if we can insert the time zone literal too. like ..
create table t1
(record_end_dtm TIMESTAMP(6) with TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ ' NOT NULL DEFAULT Timestamp '9999-12-31 11:59:00.000000 6' )

or
create table DW_STAGING.t1
(record_end_dtm TIMESTAMP(6) with TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ ' NOT NULL DEFAULT Timestamp with time zone '9999-12-31 11:59:00.000000 +00:00' )

Enthusiast

Re: Creating a Timestamp(6) with Timezone column with Default value

Thankyou Jimm! your post helped me. I was trying to define default date for a coulumn without letting the dbc know the character constant was a date :).