Categories
Data Sync Date Variable

Summary
Data Sync Date Variable
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
Answers
-
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')
0