All Forums General
tbm4bi 2 posts Joined 09/14
10 Sep 2014
Error : [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

Hi,
 
I am facing an issue while trying to insert a null value in a timestamp column.
 
Please follow the below step to get the error: [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.
 
CT STGDB_DV1.ST_TIMESTAMP_TEST
(
EMP_ID INTEGER,
GL_POSTED_DTM TIMESTAMP(0) TITLE 'GL_POSTED_DTM' COMPRESS
);

CT STGDB_DV1.ST_INT_TEST
(
EMP_ID INTEGER,
GL_POSTED_DTM INTEGER
);

INSERT INTO STGDB_DV1.ST_TIMESTAMP_TEST
SEL 1,NULL AS GL_POSTED_DTM
FROM STGDB_DV1.ST_INT_TEST
UNION ALL
SEL 2, NULL AS GL_POSTED_DTM
FROM STGDB_DV1.ST_INT_TEST;
 
If I am inserting a null value directly in a Timestamp column(without using union all) it is not throwing any error.
 
 
In order to avoid this issue. I had to CAST null value as Timestamp(0)
 
INSERT INTO STGDB_DV1.ST_TIMESTAMP_TEST
SEL 1,CAST(NULL AS TIMESTAMP(0))
FROM STGDB_DV1.ST_INT_TEST
UNION ALL
SEL 2,CAST(NULL AS TIMESTAMP(0))
FROM STGDB_DV1.ST_INT_TEST;
 
 
Please explain the mechanism of handling nulls to insert into Timestamp columns.

Bhanu
dnoeth 4628 posts Joined 11/04
10 Sep 2014

A NULL in Teradata has a datatype (just don't ask me why).
SELECT TYPE(NULL) returns INTEGER and you can't insert an INT into a TIMESTAMP column.

Dieter

tbm4bi 2 posts Joined 09/14
11 Sep 2014

But when i am trying to insert from a single table as mentioned below, why it is not throwing any error?
 
INSERT INTO STGDB_DV1.ST_TIMESTAMP_TEST
SEL 1,NULL AS GL_POSTED_DTM
FROM STGDB_DV1.ST_INT_TEST
 
 

Bhanu

narainD 1 post Joined 05/15
07 May 2015

Hi,
 
I am facing an issue while trying to updating row 
teradata error 5407 invalid operation for datetime or interval
 

update park1 set dtime=(select end_time- start_time hour to second "time" from park1 where slot_id=400)

    where slot_id=400;

Fred 1096 posts Joined 08/04
10 May 2015

What is the datatype of dtime? Is it INTERVAL HOUR TO SECOND or INTERVAL DAY TO SECOND?

You must sign in to leave a comment.