I am on APEX 24.2.8
I have an upload process wherein fie is loaded into apex_application_temp_files and inserted into an intermediate table where all columns are varchar2.
Scenario 1 - Excel File comes in with sample date mm/dd/yyyy hh24:mi:ss and is uploaded into table into respective sample_date column which is declared as varchar2 using apex_data_parser. Without any date formatting applied at this point, the data shows in yyyy-mm-dd hh24:mi format in the sample_date field. Process then inserts this into final table with a to_Date(sample_date,'yyyy-mm-dd hh24:mi:ss') format applied. All is fine and process ends successfully.
Scenario2 - File comes in with sample date mm/dd/yyyy hh24:mi:ss and is uploaded into table into the respective sample_date column which is declared as varchar2 in mm/dd/yyyy hh24:mi format. Not sure why it changed here? Same Process as above then tries to insert this into final table with a to_Date(sample_date,'yyyy-mm-dd hh24:mi:ss') format applied. Now of course it gives an invalid month error.
Am not very clear on what just happened here. Why did it store date from one file in yyyy-mm-dd format while anohter in mm/dd/yyyy format.
I did try to_char(to_date(sample_date,'mm/dd/yyyy'),'yyyy-mm-dd') while apex_data_parsing but that also gave error.
Appreciate your insights.
Thank you!