FAQ: What format are the dates in the ArcSDE schema tables stored?
What format are the dates in the ArcSDE schema tables stored?
In the ArcSDE schema tables, the dates are stored as a number that represent the time the date was created, in seconds since 1970. Examples of dates stored in this format are the SDE.LAYERS.CDATE and SDE.TABLE_REGISTRY.REGISTRATION_DATE. 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