Oracle 21c

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

CREATE TABLE table_name (value) AS
SELECT '2022-01-21 2:49:38.251'  FROM DUAL UNION ALL
SELECT '22-01-21 2:49:38.251000' FROM DUAL;
2 rows affected

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH24.MI.SSXFF';
SELECT value,
       TO_CHAR(
         CAST(value AS TIMESTAMP),
         'YYYY-MM-DD HH24:MI:SS.FF'
       ) AS converted_value
FROM   table_name;
ORA-01843: not a valid month

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'RR-MM-DD HH24:MI:SSXFF'
SELECT value,
       TO_CHAR(
         CAST(value AS TIMESTAMP),
         'YYYY-MM-DD HH24:MI:SS.FF'
       ) AS converted_value
FROM   table_name;


VALUE
CONVERTED_VALUE


2022-01-21 2:49:38.251
2022-01-21 02:49:38.251000

22-01-21 2:49:38.251000
2022-01-21 02:49:38.251000


ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF'
SELECT value,
       TO_CHAR(
         CAST(value AS TIMESTAMP),
         'YYYY-MM-DD HH24:MI:SS.FF'
       ) AS converted_value
FROM   table_name;


VALUE
CONVERTED_VALUE


2022-01-21 2:49:38.251
2022-01-21 02:49:38.251000

22-01-21 2:49:38.251000
0022-01-21 02:49:38.251000

VALUE	CONVERTED_VALUE
2022-01-21 2:49:38.251	2022-01-21 02:49:38.251000
22-01-21 2:49:38.251000	2022-01-21 02:49:38.251000