Adding Data to a table that has TimeStamp(0) defined field

Database

Adding Data to a table that has TimeStamp(0) defined field

I am trying to add data to a table that has a field in it defined as TIMESTAMP(0). I have tried many different ways to accomplish this.

The table is defined as follows:
CREATE SET TABLE TEMP1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(UNQ_ORD_ID CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SRC_FILE_LOAD_DT TIMESTAMP(0))
UNIQUE PRIMARY INDEX ( UNQ_ORD_ID );

- I have tried inserting as straight values (eg.
insert into TEMP1
values('this is a test',current_timestamp);
*****response from teradata is INVALID DATE

-I have created a temp table that has a field defined as char(14) and tried inserting into my TEMP1 table from this table with all different types of casting and formats with no success. This is the data from my table with the CHAR(14) data in it.
UNQ_ORD_ID SRC_FILE_LOAD_DT
this is a test 20080813111800

I need to know what the correct syntax is if anyone can help

Thanks
5 REPLIES
Enthusiast

Re: Adding Data to a table that has TimeStamp(0) defined field

I think that the default precision for "current_timestamp" is 6, that is, current_timestamp(6). Try explicitly using "current_timestamp(0)" in your insert statement. To further illustrate the difference, run this sql in TSA: sel current_timestamp (varchar(32)), current_timestamp(0) (varchar(32))

Re: Adding Data to a table that has TimeStamp(0) defined field

Current TimeStamp(6) Current TimeStamp(0)
2008-08-13 11:12:15.000000+00:00 2008-08-13 11:12:15+00:00
Enthusiast

Re: Adding Data to a table that has TimeStamp(0) defined field

insert into TEMP1
values('this is a test',current_timestamp(0))

Re: Adding Data to a table that has TimeStamp(0) defined field

OK there is something wrong with either my ID or Teradata SQL Assistant. I ran that query in TSA and it abended with invalid date. I ran the same insert query via Batch and it ran OK. I have the DBA's looking into my ID and then they gonna look at my TSA maybe I have some settings wrong in TSA.

The query I am talking about is

insert into TEMP1
values('this is a test',current_timestamp(0));

It worked via Batch but not TSA.

Thanks to all who replied. If anyone knows what settings i might have incorrect in TAS that would be helpful.

Enthusiast

Re: Adding Data to a table that has TimeStamp(0) defined field

In the TD-ODBC (3.05) Advanced Options, there is a setting for "use DATE data for TIMESTAMP parameters". My setting is "unchecked". I don't know if this is your problem or not - just a guess.