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

General

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

Can somebody help please . Thanks in advance :)

SELECT 

StartDate, GRP_CD, Sum(ORG_SCH_STAFF_VAL) "Plan_Staff", SUM(ORIG_FCST_VOL) "Plan_Offered"

FROM 

(SELECT 

to_char(to_date('30-12-1899', 'DD-MON-YYYY') +

 ((forDet.STRT_TM - (SELECT BIAS_IN_MIN FROM EWFM_TM_ZN_DTL TZ 

WHERE TZ. TM_ZN_ID = forgrp. TM_ZN_ID AND

to_char(to_date('30-12-1899', 'DD-MON-YYYY') + forDet.STRT_TM/1440 ,'DD-MON-YYYY') 

BETWEEN STRT_DTS AND STP_DTS))/1440),'DD-MM-YYYY') StartDate -- EWFM_TM_ZN_DTL

,forgrp.GRP_CD

, ORG_SCH_STAFF_VAL -- SVC_BASE_T.EWFM_IDP_STAFF_GRP_DTL

, ORIG_FCST_VOL -- SVC_BASE_T.EWFM_IDP_FCST_GRP_DTL

FROM EWFM_FCST_GRP forgrp

JOIN EWFM_STAFF_GRP stfgrp ON stfgrp.GRP_CD = forgrp.GRP_CD

JOIN EWFM_IDP_FCST_GRP_DTL forDet ON forgrp. FCST_GRP_ID = forDet. FCST_GRP_ID

JOIN EWFM_IDP_STAFF_GRP_DTL stfDet ON stfGrp.STAFF_GRP_ID = stfDet. STAFF_GRP_ID AND forDet.STRT_TM = stfDet. STRT_TM

WHERE  to_char(to_date('30-12-1899', 'DD-MON-YYYY') + ((forDet.STRT_TM)/1440),'DD-MM-YYYY') >= to_char(sysdate - 9, 'DD-MM-YYYY') 

AND to_char(to_date('30-12-1899', 'DD-MON-YYYY') + ((forDet.STRT_TM )/1440),'DD-MM-YYYY') <= to_char(sysdate + 9, 'DD-MM-YYYY'))  b

GROUP BY startDate, GRP_CD

2 REPLIES
Senior Apprentice

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

That's an Oracle query, of course it fails on Teradata, date/time handling is totally different.

Is this trying to adjust timezones? And comparing timestamps using TO_CHAR is very strange.

You need to specify more details on the actual data and what this query is trying to do...

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

it is trying to adjust time zones . Can you help in converting this query to teradata .