Search code examples
sqloracle-database

Getting error as ORA-01821: date format not recognized in Oracle

I have a column in select statement where I am getting date like below

2017-12-21T12:00:44.064+05:30 and the column name is RFI_DATE but it's data type is NVARCHAR2.

but while I am selecting it like below

select sap_id, to_date(RFI_DATE, 'YYYY-MM-DD HH24:MI:SS') from IPCOLO_IPFEE_CALC_BIL;

I am getting error as

ORA-01821: date format not recognized

Solution

  • Have a look at your data. String '2017-12-21T12:00:44.064+05:30' does not match format 'YYYY-MM-DD HH24:MI:SS'

    DATE data type does not support fractional seconds nor time zones. If you like to use them, you must use TIMESTAMP WITH TIME ZONE data type. Would be this one:

    TO_TIMESTAMP_TZ(RFI_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')