Error : [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

General
Fan

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.

4 REPLIES
Junior Contributor

Re: Error : [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

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.

Fan

Re: Error : [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

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

Re: Error : [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

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;

Teradata Employee

Re: Error : [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval.

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