How to solve ora-01841 in oracle sql during change my date

I have timestamp with timezone. for example 2023-02-28T03:34:11.249+03:30 I change my date with

To_char(to_timestamp_tz('column_name','YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM'),'YYYYMMDDHH24MISS','NLS_CALENDAR=PERSIAN') FROM TBL

I get error ora-01841:(full) year must be between -4713 and +9999, and not be 0 How can I solve it?

I'm supposed to have some data ( year) not fixed to my formula. ps: my column_name datatype is varchar2() so I have to write 'column_name' not "column_name".

Thanks

Solution

As mentioned in comments, 'column_name' is a string literal and is not an identifier. You either want an unquoted identifier column_name or a quoted identifier "column_name" (with double quotes, not single, and this would be case-sensitive) or, if you do want to use a string literal then provide a valid timestamp string '2023-02-28T03:34:11.249+03:30'.

With an unquoted identifier, your code works:

SELECT To_char(
         to_timestamp_tz(
           column_name,
           'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM'
         ),
         'YYYYMMDDHH24MISS',
         'NLS_CALENDAR=PERSIAN'
       ) as persion_ts
FROM   TBL

If you have invalid timestamp strings in the table then, from Oracle 12, you can use DEFAULT NULL ON CONVERSION ERROR to handle invalid values:

SELECT To_char(
         to_timestamp_tz(
           column_name
           DEFAULT NULL ON CONVERSION ERROR,
           'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM'
         ),
         'YYYYMMDDHH24MISS',
         'NLS_CALENDAR=PERSIAN'
       ) as persion_ts
FROM   TBL

fiddle