Create a table with a date column that has a default lenght (8)

Database

Create a table with a date column that has a default lenght (8)

Hi All!

We can not create a table adding a column DATE that has lenght(8) using FORMAT (YYYYMMDD) and default value '00010101'.

Nir 20010101 nor 20000101 default values are not working.

currently We are running the DDL USING:

FEC_INFORMACION        DATE           FORMAT 'YYYYMMDD' NOT NULL DEFAULT DATE '0001-01-01'

We need to have default date value: '19000101' or '20010101' or 'YYYYMMDD' LENGHT (8)

The SQL ASSISTANT is failing by passing:

FEC_INFORMACION        DATE           FORMAT 'YYYYMMDD' NOT NULL DEFAULT DATE '00010101'

Can someone please assist me with the TERADATA ISSUE?.

3 REPLIES
Senior Supporter

Re: Create a table with a date column that has a default lenght (8)

It is always easier if you share some example code...

Some clarification - a date data type is stored as 4 byte. Always.

What you can specify is the format how the date is shown, but this does not change the way the date is stored.

The below worked for me...

 *** Logon successfully completed.
*** Teradata Database Release is 14.10.00.00
*** Teradata Database Version is 14.10.00.00
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

create table test_db.a_dt
( pk integer,
FEC_INFORMACION DATE FORMAT 'YYYYMMDD' not null DEFAULT
date '1901-01-01'
)
no primary index;

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

insert into test_db.a_dt (pk) values (1);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
insert into test_db.a_dt(2,date);

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

select * from test_db.a_dt;

*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

pk FEC_INFORMACION
----------- ---------------
1 19010101
2 20140703

+---------+---------+---------+---------+---------+---------+---------+----

drop table test_db.a_dt;

*** Table has been dropped.
*** Total elapsed time was 1 second.

Re: Create a table with a date column that has a default lenght (8)

Hi Ulrich,

Thanks for the above example

I can create the table as mentioned above, however I'm inserting the data from a Excel file

I've tried the below statements but does not seem to work

INSERT INTO DB.Table (?,?,?,?,?,?,?,?,date);

INSERT INTO DB.Table (?,?,?,?,?,?,?,?);

INSERT INTO DB.Table (?,?,?,?,?,?,?,?,?);

Any help would be greatly appreciated

Thanks,

Dominic

Enthusiast

Re: Create a table with a date column that has a default lenght (8)

what does your data actually look like for your insert statement? (at least format-wise).  Is the issue that the date your trying to insert isn't working or that when you try to insert for a null date the default value isn't working (which would be odd).  Can you be a bit more descriptive in the actual issue you are encountering and an example of data that is failing (without sharing anything that would be considered sensative).