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 |