TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

UDA
N/A

TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

I have the following case statement that works just fine in SQL Assistant..but fails when I move to batch JCL.. and gives me a 5407 error. (Failure 5407 Invalid operation on an ANSI Datetime or Interval value)

Any ideas. What I need to do is adjust a reported time into a new time zone value.

,TZ
,CAST((ADTE || ' ' || CAST((ATME*100) AS TIME)) AS TIMESTAMP(0)) AS AUD_DTME
,CASE
WHEN TZ = 'NT' THEN ( AUD_DTME - interval '02:30:00' hour to second)
WHEN TZ = 'TT' THEN ( AUD_DTME - interval '02:00:00' hour to second)
WHEN TZ = 'ET' THEN ( AUD_DTME - interval '01:00:00' hour to second)
WHEN TZ = 'CT' THEN (AUD_DTME)
WHEN TZ = 'MT' THEN (AUD_DTME + interval '01:00:00' hour to second)
WHEN TZ = 'PT' THEN (AUD_DTME + interval '02:00:00' hour to second)
WHEN TZ = 'AT' THEN (AUD_DTME + interval '03:00:00' hour to second)
ELSE AUD_DTME
END AS TCS_DTME
7 REPLIES
Teradata Employee

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

I suspect you have the "allow use of ODBC extensions" option checked in SQL Assistant, and the DDL and/or DML you enter is being modified by the ODBC driver before sending it to Teradata.

Do a SHOW TABLE to see the real data types (especially for ATME). Fix the DDL if necessary (run from BTEQ or with ODBC extensions disabled). Also think carefully about both implicit and explicit type conversions you are trying to do. In particular, CAST(ATME*100 AS TIME) is unlikely to be valid. It seems what you really are trying to do is format the date and time as character strings, concatenate the text, and convert the result to TIMESTAMP. Don't bother trying to make fields DATE or TIME types first - just focus on getting the character representation correct.

For example, if ATME is SMALLINT, you could use something like this:
CAST(CAST(ATME AS FORMAT '99:99') AS CHAR(5))||':00'

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

Similar problem, in that I'm trying to compare two timestamp variables:

WHERE CAST(C.PROC_DTTM AS TIMESTAMP(6))
BETWEEN T2.PROC_DTTM - CAST(12 AS INTERVAL HOUR)
AND T2.PROC_DTTM

T2.PROC_DTTM, from a temp table is TIMESTAMP(6)
C.PROC_DTTM is CHAR(26)

*** Failure 5407 Invalid operation on an ANSI Datetime or Interval value.

All data values for C.PROC_DTTM and T2.PROC_DTTM have valid data.

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)



Try formatting your CHAR(26) to TIMESTAMP(6):

SELECT 'TIMESTAMP OK'
WHERE CAST('2009/06/25 16:00:00.000000' AS TIMESTAMP(6) FORMAT 'YYYY/MM/DDBHH:MI:SS.S(6)' )
BETWEEN CURRENT_TIMESTAMP - CAST(12 AS INTERVAL HOUR)
AND CURRENT_TIMESTAMP;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

'TIMESTAMP OK'
--------------
TIMESTAMP OK

HTH.

Cheers.

Carlos.

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

WHERE CAST(C.PROC_DTTM AS TIMESTAMP(6)
FORMAT 'YYYY/MM/DDBHH:MI:SS.S(6)')
BETWEEN T2.PROC_DTTM - CAST(12 AS INTERVAL HOUR)
AND T2.PROC_DTTM
;
*** Failure 5407 Invalid operation on an ANSI Datetime or Interval value.

Adding the FORMAT doesn't seem to have helped. Any other ideas would be greatly appreciated.

Fernando

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

>>Adding the FORMAT doesn't seem to have helped. Any other ideas would be greatly appreciated.

>>Fernando

I posted an EXAMPLE of format to show it works. I don't know the format you use to store timestamps as characters (very bad practice, by the way). You should use a format that meets your needs.

Cheers.

Carlos.

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

Carlos,

As it turns out, we do store timestamps as CHAR(23)... I finally got this logic to work:

WHERE CAST(C.PROC_DTTM AS TIMESTAMP(6)
FORMAT 'YYYY-MM-DD-HH:MI:SS.S(6)')
BETWEEN CAST(T2.PROC_DTTM AS TIMESTAMP(6)
FORMAT 'YYYY-MM-DD-HH:MI:SS.S(6)')
- CAST(12 AS INTERVAL HOUR)
AND CAST(T2.PROC_DTTM AS TIMESTAMP(6)
FORMAT 'YYYY-MM-DD-HH:MI:SS.S(6)')

Thanks for your help,

Fernando

Re: TIMESTAMP INTERVAL NOT WORKING IN BATCH JCL (BUT WORKS IN SQL ASSISTANT)

Fred's solution turning off ODBC extensions worked for my example too:
SELECT CAST((TIME '09:30:22' - INTERVAL '01:20:10.45' HOUR TO SECOND) AS TIME(2));