Data Sync Date Variable

Question

Summary

Content

Hi,

I created a Variable "$$INITIAL_EXTRACT_DATE" with Below details

While trying to use this variable in date comparison or insert the value in a Date field getting below Error.

"ORA-01858: a non-numeric character was found where a numeric was expected".

When i check the log file the value is coming as TO_DATE('$$20060101IAL_EXTRACT_DATE','MM-DD-YYYY HH24:MI:SS') AS "EXTRACT_DATE"

Thanks

User743400-Oracle · Answer

You do not need $$ prefix.  just create a variable called INITIAL_EXTRACT_DATE with custom formatting - in this case use format as MM-dd-yyyy HH:mm:ss (Java notation - refer to https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html).

While using in a query refer to the parameter as

select *,  to_date('%%INITIAL_EXTRACT_DATE', 'MM-DD-YYYY HH24:MI:SS') as initial_extract_date

from my_table

where some_dt > to_date('%%INITIAL_EXTRACT_DATE', 'MM-DD-YYYY HH24:MI:SS')