Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE FUNCTION createDate(
  year   int,
  month  int,
  day    int,
  hour   int,
  minute int,
  second int
) RETURN DATE DETERMINISTIC
IS
  hex CHAR(14);
  d DATE;
BEGIN
  hex := TO_CHAR( FLOOR( year / 100 ) + 100, 'fm0X' )
      || TO_CHAR( MOD( year, 100 ) + 100, 'fm0X' )
      || TO_CHAR( month, 'fm0X' )
      || TO_CHAR( day, 'fm0X' )
      || TO_CHAR( hour + 1, 'fm0X' )
      || TO_CHAR( minute + 1, 'fm0X' )
      || TO_CHAR( second + 1, 'fm0X' );
  DBMS_OUTPUT.PUT_LINE( hex );
  DBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), d );
  RETURN d;
END;
/
CREATE TABLE table_name ( value DATE );
INSERT INTO table_name ( value )
SELECT DATE '1900-01-01' FROM DUAL UNION ALL
SELECT createDate( 1900, 1, 0, 0, 0, 0 ) FROM DUAL;
2 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT value,
       TO_CHAR( value, 'YYYY-MM-DD HH24:MI:SS' ) AS string_value
FROM   table_name;


VALUE
STRING_VALUE


1900-01-01 00:00:00
1900-01-01 00:00:00

1900-01-00 00:00:00
0000-00-00 00:00:00


UPDATE table_name
SET    value = NULL
WHERE  EXTRACT( DAY FROM value ) = 0;
1 rows affected

SELECT value,
       TO_CHAR( value, 'YYYY-MM-DD HH24:MI:SS' ) AS string_value
FROM   table_name;


VALUE
STRING_VALUE


1900-01-01 00:00:00
1900-01-01 00:00:00

null
null

VALUE	STRING_VALUE
1900-01-01 00:00:00	1900-01-01 00:00:00
1900-01-00 00:00:00	0000-00-00 00:00:00