Trying to load from DB2 to Oracle using DataStage getting this error. It is loaded some but not all data. DS fails & gives this error:
The OCI function OCIStmtExecute returned status 1. Error code: 1849, Error message: ORA-01849: hour must be between 1 and 12.
Have this in the SQL:
SELECT
SUBSTR(CHAR(DTE_REC_ODR), 9,2)||'-'||SUBSTR(UPPER(MONTHNAME(DTE_REC_ODR)),1,3)||'-'||SUBSTR(CHAR(DTE_REC_ODR),1,4) AS DTE_REC_ODR,
SUBSTR(CHAR(DTE_BLG_FSN), 9,2)||'-'||SUBSTR(UPPER(MONTHNAME(DTE_BLG_FSN)),1,3)||'-'||SUBSTR(CHAR(DTE_BLG_FSN),1,4) AS DTE_BLG_FSN,
SUBSTR(VARCHAR_FORMAT(COALESCE(VSC_ASGN_TS,CURRENT TIMESTAMP),'YYYY-MM-DD HH24:MI:SS'),9,2)
||'-'||SUBSTR(UPPER(MONTHNAME(COALESCE(VSC_ASGN_TS,CURRENT TIMESTAMP))),1,3)
||'-'||SUBSTR(VARCHAR_FORMAT(COALESCE(VSC_ASGN_TS,CURRENT TIMESTAMP),'YYYY-MM-DD HH24:MI:SS'),1,4)
|| ' ' ||
CASE
WHEN HOUR(VSC_ASGN_TS) < 12
THEN CHAR(TIME(VSC_ASGN_TS)) || ' AM'
WHEN HOUR(VSC_ASGN_TS) = 12
THEN CHAR(TIME(VSC_ASGN_TS)) || ' PM'
WHEN HOUR(VSC_ASGN_TS) = 0
THEN CHAR(TIME(VSC_ASGN_TS) + 12 HOUR) || ' AM'
WHEN HOUR(VSC_ASGN_TS) > 12
THEN CHAR(TIME(VSC_ASGN_TS) - 12 HOUR) || ' PM'
END AS VSC_ASGN_TS
FROM TABLE_NAME;
Any ideas anyone ... Thanks