SQL -999 INVALID INTERNAL DATE

Document ID : KB000095137
Last Modified Date : 09/05/2018
Show Technical Document Details
Question:
How to determine what data is causing the Datacom SQL request to get INVALID INTERNAL DATE error:

SQLCODE = -999, SQLSTATE=56S19
MESSAGE = INTERNAL ERROR (charDate LINE 1149): INVALID INTERNAL DATE FOUND
Answer:
This SELECT should list any rows with invalid date values. 

Substitute DATECHK with the name of the Table and COL1 with the name of the date column to check.
Add additional columns such as the key(s) to the request to further identify and isolate the bad rows..
 
SELECT substr(hex(COL1),1,2) AS CC, 
substr(hex(COL1),3,2) AS YY, 
substr(hex(COL1),5,2) AS MM, 
substr(hex(COL1),7,2) AS DD 
FROM DATECHK 
WHERE NOT ( 
substr(hex(COL1),1,2) BETWEEN '01' AND '14' 
AND substr(hex(COL1),3,2) BETWEEN '00' AND '63' 
AND (substr(hex(COL1),5,2) BETWEEN '01' AND '09' 
OR substr(hex(COL1),5,2) IN ('0A','0B','0C') ) 
AND (substr(hex(COL1),7,1) BETWEEN '0' AND '3' 
OR substr(hex(COL1),8,1) BETWEEN '0' AND '9' 
OR substr(hex(COL1),8,1) IN ('A','B','C','D','E','F') ) ) 
Additional Information:
In this example, the SELECT request was run via the DBSQLPR utility.
Five rows with bad value in the specified date column were the result:
 CC           YY           MM           DD             
 CHAR(2) N.N. CHAR(2) N.N. CHAR(2) N.N. CHAR(2) N.N.   
 ____________ ____________ ____________ ____________   
 65           26           E8           D5             
 F0           F1           F1           F1             
 E3           E8           D5           C8             
 F3           F0           F7           14             
 E8           D5           C8           D2             
 ___ 5 rows returned ___     


Adding key(s) to the SELECT will help identify the bad rows.