Oracle 11g Release 2

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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 ( date_column DATE );
INSERT INTO table_name ( date_column )
VALUES ( DATE '2019-12-31' + INTERVAL '1:02:03' HOUR TO SECOND );
1 rows affected

INSERT INTO table_name ( date_column ) VALUES ( createDate( 15599, 12, 31, 1, 2, 3 ) );
1 rows affected

dbms_output:
FFC70C1F020304

INSERT INTO table_name ( date_column ) VALUES ( createDate( 12017, 2, 21, 0, 0, 0 ) );
1 rows affected

dbms_output:
DC750215010101

SELECT DUMP( date_column ),
       TO_CHAR( date_column, 'YYYY-MM-DD' ) AS value,
       TO_CHAR( EXTRACT( YEAR FROM date_column ), 'fm00000' )
         || '-' || TO_CHAR( EXTRACT( MONTH  FROM date_column ), 'fm00' )
         || '-' || TO_CHAR( EXTRACT( DAY    FROM date_column ), 'fm00' )
         || ' ' || TO_CHAR( EXTRACT( HOUR   FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
         || ':' || TO_CHAR( EXTRACT( MINUTE FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
         || ':' || TO_CHAR( EXTRACT( SECOND FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
         AS full_value
FROM table_name;


DUMP(DATE_COLUMN)
VALUE
FULL_VALUE


Typ=12 Len=7: 120,119,12,31,2,3,4
2019-12-31
02019-12-31 01:02:03

Typ=12 Len=7: 255,199,12,31,2,3,4
0000-00-00
15599-12-31 01:02:03

Typ=12 Len=7: 220,117,2,21,1,1,1
0000-00-00
12017-02-21 00:00:00

DUMP(DATE_COLUMN)	VALUE	FULL_VALUE
Typ=12 Len=7: 120,119,12,31,2,3,4	2019-12-31	02019-12-31 01:02:03
Typ=12 Len=7: 255,199,12,31,2,3,4	0000-00-00	15599-12-31 01:02:03
Typ=12 Len=7: 220,117,2,21,1,1,1	0000-00-00	12017-02-21 00:00:00