All Forums Database
MgajsK 9 posts Joined 11/09
13 Oct 2010
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.

Jimm 298 posts Joined 09/07
13 Oct 2010

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' )

MgajsK 9 posts Joined 11/09
14 Oct 2010

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' )

Subbu99 4 posts Joined 05/05
16 Jul 2012

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 :).

Thanks,
Subbu

You must sign in to leave a comment.