Date coming with "00/01/1900"

Question

We have some scheduler processes that do some processing and insert records into multiple tables. For some records, the insert date stored is having "00/01/1900 00:13:00.634588 +00:13" as the value instead of the time that the request was processed.

I have raised with my DBA and he said i should check the application. The scheduler processes are CURRENT_TIMESTAMP and SYSDATE to insert into our tables.

Some of the tables that uses trigger are having same issue as well. Also, this is not happening all the time.

Can you suggest what i need to do or check and it is currently causing a lot of data inconsistent issues with transaction reporting.

Below is the trigger statement.

   CREATE OR REPLACE TRIGGER CB_SCHEDULES_UPD_TRIG   
   BEFORE UPDATE ON CB_SCHEDULES
   FOR EACH ROW
   BEGIN

   :NEW.LAST_MODIFIED_DATE_D := CURRENT_TIMESTAMP;

END;

the LAST_MODIFIED_DATE_D column is defined as TIMESTAMP(6) WITH TIME ZONE with default value as CURRENT_TIMESTAMP

Answer 1

There are two possible scenarios:

1. You have a valid TIMESTAMP WITH TIME ZONE but the error is in formatting it for display.

   You can specify an explicit format model to check the data:

   SELECT TO_CHAR(your_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF TZD')
            AS formatted_timestamp
   FROM   your_table;
   

   In this case you need to fix how your timestamp is being displayed (but you do not need to fix the data).

2. You have invalid data. A TIMESTAMP (and a DATE) is a binary data type and when third-party database drivers (such as JDBC, etc.) insert a TIMESTAMP in the correct binary format then no error checking is performed by the database (as it is assumed that any checks are carried out by that third-party and it is not required/desired to repeat the checks in the database). If this is the case and invalid data is inserted bypassing checks in the third-party application then there is nothing you can do other than:

   • Try to manually fix the erroneous values; and
   • Debug the third-party application and work out how it is putting the incorrect values in.