[6760] Invalid timestamp

Database
Fan

[6760] Invalid timestamp

Hello

I'm trying to write a select statement on a database but keep getting a time stamp error. The field I'm trying to select is a TIMESTAMP(6) in the format of 14/11/2012 16:24:18.071000

Whichever way I've tried my WHERE date ='14/11... I get a timestamp error. I've tried casting as date but still get the issue.

Can anyone help?

Thanks

7 REPLIES
Junior Contributor

Re: [6760] Invalid timestamp

The easiest way to write a Timestamp literal is TIMESTAMP 'yyyy-mm-dd hh:mi:ss':

WHERE col = TIMESTAMP '2012-11-14 16:24:18.071000'

If you really need to use a different format you must use a CAST plus FORMAT:

WHERE col = CAST('14/11/2012 16:24:18.071000' AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:ss.s(6)')
Fan

Re: [6760] Invalid timestamp

Thanks Dieter. That's working but I'm trying to return all the records for just a chosen date and not the time. Apologies if that wasn't clear in my original post. This is returning by date and time

Enthusiast

Re: [6760] Invalid timestamp

You need to bring both side to same data type.

WHERE CAST(col AS DATE) = CAST('14/11/2012' AS DATE FORMAT 'dd/mm/yyyy')
Junior Contributor

Re: [6760] Invalid timestamp

You can also write a DATE literal:

WHERE col = DATE '2012-11-14'
or
WHERE col = CAST('14/11/2012' AS DATE FORMAT 'dd/mm/yyyy')

There's no need for CAST(col AS DATE), the time portion is automatically stripped off when you compare a timestamp to a date. 

Re: [6760] Invalid timestamp

Hi Dnoeth,

 

I too have been facing the same error '6760: Invalid timestamp' but my scenario is different. I am trying to load into a table using a flat file by using import data option in Teradata SQL Assistant. When inserting using an insert command, it's runninh fine but creating problem when using import option. PFB the details. 

 

Here only the fields in red color are timestamp(0) data type. This insert statement runs fine.

SyntaxEditor Code Snippet

insert into table values 
('IS MPS','TesterGrp','T2000','2000-120-742','T3P','RUN_RATE','32','2017',74417,NULL,,NULL,'WFDS PS','WFDS_PS - Onetime Pop', current_timestamp(0) ,'ACTIVE',NULL,NULL,'INSERT',NULL,NULL)

 

Using the below statement is causing problem. I tried typecasting too, but no luck.  

SyntaxEditor Code Snippet

insert into table  (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

insert into table (?,?,?,?,?,?,?,?,?,?,?,?,?,?,cast(? as timestamp(0) format 'YYYY-MM-DD HH:MI:SS'),?,?,?,?,?,?)

insert into table (?,?,?,?,?,?,?,?,?,?,?,?,?,?,cast(? as timestamp(0) format 'YYYY-MM-DD HH:MI:SS'),?,?,cast(? as timestamp(0) format 'YYYY-MM-DD HH:MI:SS'),?,?,?)

 

PFB the sample data from the file i am trying to load 

 

'IS MPS','TesterGrp','T2000','2000-120-742','T3P','RUN_RATE','32','2017',74417,NULL,NULL,NULL,'WFDS PS','WFDS_PS - Onetime Pop',current_timestamp(0),'ACTIVE',NULL,NULL,'INSERT',NULL,NULL
'IS MPS','TesterGrp','T2000','2000-120-742','T3P','RUN_RATE','33','2017',74417,NULL,NULL,NULL,'WFDS PS','WFDS_PS - Onetime Pop',current_timestamp(0),'ACTIVE',NULL,NULL,'INSERT',NULL,NULL
'IS MPS','TesterGrp','T2000','2000-120-742','T3P','RUN_RATE','34','2017',74417,NULL,NULL,NULL,'WFDS PS','WFDS_PS - Onetime Pop',current_timestamp(0),'ACTIVE',NULL,NULL,'INSERT',NULL,NULL

 

 

Regards,

Amit

 

 

Junior Contributor

Re: [6760] Invalid timestamp

Do you actually have current_timestamp(0) in your input data?

Of course this is failing, it's a SQL keyword, Teradata expects a literal Timestamp instead.

 

If there's no other value but current_timestamp(0) you can simply remove it from the input file and use the keyword instead:

insert into table (?,?,?,?,?,?,?,?,?,?,?,?,?,?,current_timestamp(0),?,?,?,?,?,?)

Otherwise you can apply some NULLIF logic:

insert into table (?,?,?,?,?,?,?,?,?,?,?,?,?,?,cast(NULLIF(?, 'current_timestamp(0)' as timestamp(0) format 'YYYY-MM-DD HH:MI:SS'),?,?,?,?,?,?)

This should also work without the CAST.

 

 

Re: [6760] Invalid timestamp

Thanks Dnoeth. Actually i was under the impression that if it's working in normal insert statement, it would work by passing it as the value for an argument too by using import data option.

 

I tried like this as well, but didn't work. Throws the same error. Same with the NULLIF(with/without CAST) logic.

insert into table (?,?,?,?,?,?,?,?,?,?,?,?,?,?,current_timestamp(0),?,?,?,?,?,?)

Nonetheless, instead of current_timestamp(0), i tried passing a literal timestamp value too from my i/p data but got the same error. 

'IS MPS','TesterGrp','T2000','2000-120-742','T3P','RUN_RATE','32','2017',74417,NULL,,NULL,'WFDS PS','WFDS_PS - Onetime Pop','2017-10-24 11:26:19','ACTIVE',NULL,NULL,'INSERT',NULL,NULL

 

But then since i was running out of time, i loaded the table by building a graph in Ab Initio and was able to load it.