Yes, date Typ=12 Len=7: 119,152,121,0,54,49,54 is corrupted. Type 12 date internal format is:

Byte 1: Century (stored in excess-100 notation, meaning 100 is added to the actual century value).
Byte 2: Year-of-century (also stored in excess-100 notation).
Byte 3: Month.
Byte 4: Day.
Byte 5: Hour (stored in excess-1 notation, meaning 1 is added to the actual hour value, so 1 AM is stored as 2).
Byte 6: Minute (stored in excess-1 notation).
Byte 7: Second (stored in excess-1 notation).

So we have:

SQL> select  (119 - 100) * 100 + (152 - 100) yyyy,

  2          121 mm,

  3          0 dd,

  4          54 - 1 hh24,

  5          49 - 1 mi,

  6          54 - 1 ss

  7    from  dual

  8  /


      YYYY         MM         DD       HH24         MI         SS

---------- ---------- ---------- ---------- ---------- ----------

      1952        121          0         53         48         53


SQL>

As you can see both month and day are bad.

The other date Typ=12 Len=7: 120,102,6,10,1,1,1 is good:

SQL> select  (120 - 100) * 100 + (102 - 100) yyyy,

  2          6 mm,

  3          10 dd,

  4          1 - 1 hh24,

  5          1 - 1 mi,

  6          1 - 1 ss

  7    from  dual

  8  /


      YYYY         MM         DD       HH24         MI         SS

---------- ---------- ---------- ---------- ---------- ----------

      2002          6         10          0          0          0


SQL>

What tool is used to populate this table?

SY.

If the code that is causing this error is this call to update a date column by using the trim function on the date column, the simple and clear answer is to eliminate this update as completely unneeded.

The TRIM function should not be used on columns that are date datatype. This function is only appropriate for string or character data. Dates are not stored as strings, they are stored in a numeric date format.

Dates are not strings, they cannot have leading or trailing spaces that should be trimmed.

Using TRIM on a date value does not do anything except use a series of implicit conversions to make the date value into a string appropriate as input to the trim function and then make the string output of the trim function into a date so that the column can be updated.

The input to trim is a string… if you pass a date to it there will be an implicit conversion to a string.

Lets look at an example:

select trim(date_column)  
from my_table

Since a date is not a string, the date value has to be implicitly converted to a string for trim to use it as an input.

Explicitly showing this conversion, the statement is actually:

select trim(to_char(date_column)) 
from my_table

The output from the trim function is a string. If you are trying to update a date column with the output of the trim function there will be an implicit conversion to a date.

update my_table 
set date_column = trim(date_column) 

is actually:

update my_table 
set date_column = to_date(trim(to_char(date_column)))

since each of these conversions is implicit, the format model is not specified and the nls_date_format for the session will be used.

if your session nls_date_format is 'yyyy-mm-dd hh:mi:ss pm', then this seemingly simple statement is actually:

update my_table 
set date_column = to_date(trim(to_char(date_column, 'yyyy-mm-dd hh:mi:ss pm')),'yyyy-mm-dd hh:mi:ss pm')

Check your session nls_date_format to see if it is some form of nonsense:

select value

from nls_session_parameters

where parameter = 'NLS_DATE_FORMAT'

…the ORA-01847 error you are getting about the day of the month being in the wrong range would only happen in cases where the value that should be the day of the month is inappropriate…like this:

select  to_date('2025-02-99', 'yyyy-mm-dd') 
from dual

to_date will look at the 99 and try to make it a day of the month and you will get the ORA-01847 error….

In your case, the exact same format will be used to do the inner implicit conversion with to_char as the outer explicit conversion with to_date… so the net effect of calling trim on a date column should always be nothing.

With regards to the other error you are seeing: ORA-01801: date format is too long for internal buffer, this is a case where the date format itself is just too large… you can add extra text to the date format model if you wish… just not too much extra text:

This will work:

select

    to_char(

        sysdate,

        'yyyy/mm/dd "frogs and cats" hh:mi'
    ) as strange_date

from dual

—2025/09/06 frogs and cats 10:58

select

    to_char(

        sysdate,

        'mm/dd "frogs and cats dancing in the rain with all manner of kitchen appliances" hh:mi'
    ) as strange_date

from dual

—ORA-01801: date format is too long for internal buffer

Remove the unneeded trim call on a column that is already a date… and check your session nls_date_format for nonsense.

Regards

Anthony Harper