how to insert 0001 year in oracle?-CodePudding

how to insert 0001 year in oracle ?

I've tried

UPDATE table1
SET 
datblock = to_timestamp('01/01/0001 00:00:00,000000000','DD/MM/RR HH24:MI:SSXFF')

but it shows as 2001, anyone can help ?

CodePudding user response：

Do not use the RR format which has a special logik to decide the century.

Use explicite YYYY format

select to_timestamp('01/01/0001 00:00:00,000000000','DD/MM/RR HH24:MI:SSXFF') yyyy from dual;

YYYY                         
-----------------------------
01.01.0001 00:00:00,000000000

CodePudding user response：

Use a timestamp literal:

UPDATE table1
SET 
datblock = TIMESTAMP '0001-01-01 00:00:00';

However, your query works as the string-to-date conversion rules mean that RR also matches RRRR.

CREATE TABLE table1 (datblock) AS
SELECT TIMESTAMP '2021-09-27 01:23:45' FROM DUAL;

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

UPDATE table1
SET datblock = to_timestamp(
                 '01/01/0001 00:00:00,000000000',
                 'DD/MM/RR HH24:MI:SSXFF'
               );

SELECT * FROM table1;

Outputs:

DATBLOCK

0001-01-01 00:00:00,000000000

DATBLOCK
0001-01-01 00:00:00,000000000

db<>fiddle here