Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Data Sync Date Variable

Received Response
21
Views
1
Comments

Summary

Data Sync Date Variable

Content

Hi,

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

image

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

  • Rank 3 - Community Apprentice

    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')

Welcome!

It looks like you're new here. Sign in or register to get started.