Oracle datetime error - year values in the tens of thousands?

Question

I am experiencing an error where some of the dates in one of my tables are displaying as over 10,000 for the year. For example, the query

SELECT 
    my_date, to_char(my_date), 
    (my_date - TO_DATE('1970-01-01','YYYY-MM-DD') ) * 86400, 
    extract(year from my_date) 
FROM my_table

returns:

01-NOV-74   00-000-00   413548617599.9999999999999999999999999997   15074
15-AUG-68   00-000-00   366017127719.9999999999999999999999999999   13568
30-SEP-62   00-000-00   375298841999.9999999999999999999999999998   13862
29-SEP-82   00-000-00   325438773599.9999999999999999999999999998   12282
01-AUG-13   01-AUG-13   1375315200  2013

Any clue what's going on here? The last date looks fine, but the rest are off. Is it some date format I'm not aware of that Oracle is using internally? Or were dates entered incorrectly to this table and I'm out of luck with retrieving the real values?

Is the column a `DATE` column or a `TIMESTAMP` column? `DATETIME` is not a valid type for a column definition in Oracle. A `DATE` column includes capturing time as well. Finding out the data type of the column is the first step to resolving your issue. — EJ Egyed, Sep 21 '20 at 12:42
Sorry, should have been more clear. The column is of type `DATE`. I also updated the question with more details. — Ryan W, Sep 21 '20 at 12:59
If you try and generate a date with 5 digits then you'll get an error ([db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=20a19bd4bb91a6db4ff984dee1ebc6dc)) but the duplicate I linked shows its, theoretically, possible to store dates with a year up to 15599 but doing so makes built-in functions like `TO_CHAR` fail (gracefully). ... — MT0, Sep 21 '20 at 14:11
You can use the linked duplicate to get year, month, day, hour, minute or second values but if you are hoping to retrieve a 4-digit year then you are out of luck as the rows have a 5-digit year and you'll have to either delete the rows or edit them to something sensible, — MT0, Sep 21 '20 at 14:11
PostgreSQL can work with dates over the year 10000 and returns `15074-11-01 08:00:00 13568-08-15 08:22:00 13862-09-30 14:20:00 12282-09-29 11:20:00` How did you manage to get those into Oracle? https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ba91583a1b96a578c2e62a69c83f5622 — dnoeth, Sep 21 '20 at 14:25
No clue how the odd dates got there. The table is in another schema managed by others, and the incorrect dates make up a very small portion of the total records in the table. Say instead of getting 5 digit dates, I had dates with years such as 4639. Would anything else come to mind as to why they might be wrong? — Ryan W, Sep 21 '20 at 14:35
@dnoeth The linked duplicate show how you can put those dates in just using Oracle code. However, its much easier to do if you are using Java/Python/C#/etc. and the database driver used uploads the date as a binary value bypassing most of the usual internal checks on the date. — MT0, Sep 21 '20 at 17:58

score 0 · Answer 1 · answered Sep 21 '20 at 12:45

If MY_DATE is date, it works OK for me:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> desc my_table
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------------
 MY_DATE                                                        DATE

SQL> select * from my_table;

MY_DATE
----------
01.11.1974
15.08.1968

SQL> SELECT
  2      my_date, to_char(my_date),
  3      (my_date - TO_DATE('1970-01-01','YYYY-MM-DD') ) * 86400 calc,
  4      extract(year from my_date)
  5  FROM my_table;

MY_DATE    TO_CHAR(MY       CALC EXTRACT(YEARFROMMY_DATE)
---------- ---------- ---------- ------------------------
01.11.1974 01.11.1974  152496000                     1974
15.08.1968 15.08.1968  -43545600                     1968

SQL>

Oracle datetime error - year values in the tens of thousands?

1 Answers1