What format are the dates in the enterprise geodatabase system tables stored?

Answer

Geodatabase system tables include the core system tables that track geodatabase objects and behavior and a few supporting tables that implement geodatabase properties in a database.

In enterprise geodatabase system tables, the dates are stored as a number that represents the time the date was created, in seconds, since 1970. Examples of dates stored in this format are the values in the SDE.LAYERS.CDATE and SDE.TABLE_REGISTRY.REGISTRATION_DATE fields.

An example of how to convert this format to an Oracle date format is shown below:

SELECT table_name,TO_CHAR(NEW_TIME(TO_DATE('01-JAN-70'),'GMT','PDT') + registration_date / 86400.0, 'Month DD, YYYY HH:MI:SS am')
FROM sde.table_registry;

For DB2 an example query would be as follows with the result being returned as GMT:

select table_name,TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')+ c.registration_date seconds from sde.table_registry c