String to Date conversion gets done via FORMAT.
date format should be:
MM/DD/YYYY HH12:MI:SS AM
The format which I have received the value is 9/4/2013 1:54:00 AM
I tried using a PL/SQL function body:
new_creation_date := TO_DATE(TO_CHAR('9/4/2013 1:54:00 AM', 'MM/DD/YYYY HH12:MI:SS AM'), 'MM/DD/YYYY');
This gave me a ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Can you tell me more about FORMAT?
You are silently casting a string to a number (not a date).
declare l_input_from_csv varchar2(20) := '9/4/2013 1:54:23 AM'; l_date date; begin l_date := to_date( l_input_from_csv, 'MM/DD/YYYY HH12:MI:SS AM'); return l_date; end;
HOWEVER -- I think APEX will convert the date data type back into a string data type (which is not what you want to happen).
Assuming you are using the Data Loader in your application:
The first page/step of the process asks for the file, separator, etc.
The second page/step asks you to assign TABLE column to CSV column (***!!!!!)
-- this also includes any special 'formatting' that needs to take place.
-- it is on THIS page that you will add the appropriate FORMAT string to the appropriate text box. (the empty box under the dropdown list)
-- from my experience, APEX will run the to_date() command using that 'format string' for you.
The third page/step is a verification step
The 4th page/step shows you what has been done.