date format error in to_date

Question

I trying to run this query :

SELECT

   to_date(substr( "SCC_STG_DIM_OVERWRITE_FNL"."TERM_END_DT" , 1, 19), 'yyyy.mm.dd hh24:mi:ss.aaaaaaaaa') 

                                                        FROM ("EDMWHS"."SERVICE_CONTRACT" "SERVICE_CONTRACT" INNER JOIN "EDMSTG"."STG_SERVICE_CONTR_DELTA" "STG_SERVICE_CONTR_DELTA" ON
                                                        ( "STG_SERVICE_CONTR_DELTA"."CRM_ORDER_OBJECT_GUID"  =  "SERVICE_CONTRACT"."CRM_ORDER_OBJECT_GUID" )) INNER JOIN
                                                        "EDMSTG"."SCC_STG_DIM_OVERWRITE_FNL" "SCC_STG_DIM_OVERWRITE_FNL" ON ( "SCC_STG_DIM_OVERWRITE_FNL"."CRM_ORDER_OBJECT_GUID"  = 
                                                        "SERVICE_CONTRACT"."CRM_ORDER_OBJECT_GUID" )
                                                        WHERE ( "SCC_STG_DIM_OVERWRITE_FNL"."RECORD_STATUS"  = 'A') and ( "SERVICE_CONTRACT"."RECORD_STATUS"  = 'A')
                                                        ORDER BY  "SERVICE_CONTRACT"."CRM_ORDER_OBJECT_GUID"  ASC ,  "SCC_STG_DIM_OVERWRITE_FNL"."CRM_ORDER_ITEM_GUID"  ASC ;

But I am getting the below error : ORA-01821: date format not recognized 01821. 00000 - "date format not recognized

Why am i getting this error, and how can I solve it?

score 0 · Answer 1 · answered Apr 02 '18 at 10:25

I believe you want:

select to_timestamp('2016-01-01 14:08:13.123456789', 'yyyy.mm.dd hh24:mi:ss.ff9')
from dual;

Your code has two errors. The fractional seconds are represented by ff, not aa. And, to_date() doesn't support fractional seconds, so you probably intend to_timestamp().

date format error in to_date

1 Answers1