All Forums UDA
rcdinc 1 post Joined 06/09
09 Jun 2009
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

Fred 1096 posts Joined 08/04
10 Jun 2009

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'

furrutia 5 posts Joined 06/09
24 Jun 2009

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_DTTMT2.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.

CarlosAL 512 posts Joined 04/08
25 Jun 2009

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 OKHTH.Cheers.Carlos.

furrutia 5 posts Joined 06/09
25 Jun 2009

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

CarlosAL 512 posts Joined 04/08
26 Jun 2009

>>Adding the FORMAT doesn't seem to have helped. Any other ideas would be greatly appreciated.>>FernandoI 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.

furrutia 5 posts Joined 06/09
26 Jun 2009

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

rupert160 131 posts Joined 09/10
26 Nov 2010

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));

You must sign in to leave a comment.