Can I find all character(8) fields where a date is stored?

Document ID : KB000097682
Last Modified Date : 23/05/2018
Show Technical Document Details
Question:
Is there any way we can identify the Date fields in all the tables we have in our Datacom system?
Environment:
z/OS 
CA Datacom
Answer:
Run these queries:
SELECT RECORD_NAME , ENTITY_NAME , AUTHID , AGR_SQLNAME , SQLNAME FROM SYSADM.FIELD WHERE SEMANTIC_TYPE = 'SQL-DATE' AND STATUS = 'P' AND ENTITY_TYPE = 'FLD' ;

SELECT RECORD_NAME , ENTITY_NAME , AUTHID , AGR_SQLNAME , SQLNAME FROM SYSADM.FIELD WHERE SEMANTIC_TYPE = 'CA-DATE' AND STATUS = 'P' AND ENTITY_TYPE = 'FLD' ;

As it turns out you store a date  in a character(8) column so not a 'date' type column.
Field PCPNT-EFDT is a character(8) field.
You know by looking at the field name that that field contains a date.
That field is not a 'date' type field as we know it in Datacom terms .
Not all fields that have been defined as character(8) are fields where a date is stored.
There is no way to list only the fields where a date is stored.
 
Following query can be used to list all char(8) fields :

SELECT DISTINCT(ENTITY_NAME), RECORD_NAME ,AUTHID , AGR_SQLNAME , SQLNAME
FROM SYSADM.FIELD
WHERE STATUS = 'P' AND ENTITY_TYPE = 'FLD' AND ENABLE = 'Y' AND TYPE = 'C' AND LENGTH = 8