Failure 6760 Invalid timestamp

Database
Enthusiast

Failure 6760 Invalid timestamp

Hi Team,

when i exec the following sql in TD SQL Assist the rec are getting inserted successfully. But I try to exec the sql through the shell script I am getting the err "Failure 6760 Invalid timestamp.  Statement# 1, Info =0"

INSERT
INTO dev.r_emmd_ItemScanHit
(
SCAN_ID
, SCAN_REQUEST_TS
, SCAN_RESPONSE_TS
, RESPONSE_STATUS_CD
, UPC_ID
, UPC_DSC
, J4U_CATEGORY_ID
, HOUSEHOLD_ID
, STORE_ID
, APPLICATION_ERROR_CD
, SCAN_REQUEST_URL
, SRC_EXTRACT_NM
)
SELECT
rfl.SCAN_ID
, (substr(rfl.SCAN_REQUEST_TS,7,4)||'-'||substr(rfl.SCAN_REQUEST_TS,1,2)||'-'||substr(rfl.SCAN_REQUEST_TS,4,2)||substr(rfl.SCAN_REQUEST_TS,11,15)) as SCAN_REQUEST_TS
, (substr(rfl.SCAN_RESPONSE_TS,7,4)||'-'||substr(rfl.SCAN_RESPONSE_TS,1,2)||'-'||substr(rfl.SCAN_RESPONSE_TS,4,2)||substr(rfl.SCAN_RESPONSE_TS,11,15
)) as SCAN_RESPONSE_TS
, rfl.RESPONSE_STATUS_CD
, rfl.UPC_ID
, rfl.UPC_DSC
, rfl.J4U_CATEGORY_ID
, rfl.HOUSEHOLD_ID
, rfl.STORE_ID
, rfl.APPLICATION_ERROR_CD
, rfl.SCAN_REQUEST_URL
, 'scan_hits_1.csv'
FROM dev.r_emmd_ItemScanHit_fl rfl;

DDL for the timestamp col: 

...

SCAN_REQUEST_TS TIMESTAMP(6) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ',

SCAN_RESPONSE_TS TIMESTAMP(6) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ',

...

sample timestamp data as follows.

04/12/2015 21:03:43+00:00

Can anyone help me to find what is wrong when i try to exec over unix shell script?

Thank you.

1 REPLY
Enthusiast

Re: Failure 6760 Invalid timestamp

It works if i make the sql as follows.

INSERT
INTO dev.r_emmd_ItemScanHit
(
SCAN_ID
, SCAN_REQUEST_TS
, SCAN_RESPONSE_TS
, RESPONSE_STATUS_CD
, UPC_ID
, UPC_DSC
, J4U_CATEGORY_ID
, HOUSEHOLD_ID
, STORE_ID
, APPLICATION_ERROR_CD
, SCAN_REQUEST_URL
, SRC_EXTRACT_NM
)
SELECT
rfl.SCAN_ID
, CAST(rfl.SCAN_REQUEST_TS AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS.S(6)BZ') as SCAN_REQUEST_TS
, CAST(rfl.SCAN_RESPONSE_TS AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS.S(6)BZ') as SCAN_RESPONSE_TS
, rfl.RESPONSE_STATUS_CD
, rfl.UPC_ID
, rfl.UPC_DSC
, rfl.J4U_CATEGORY_ID
, rfl.HOUSEHOLD_ID
, rfl.STORE_ID
, rfl.APPLICATION_ERROR_CD
, rfl.SCAN_REQUEST_URL
, 'scan_hits_1.csv'
FROM dev.r_emmd_ItemScanHit_fl rfl;