I'm creating an oracle apex page to load the csv file and to view the uploaded data.

The csv file contains three the date columns namely access reference date, access start date and access end date, and the requirement is to display as given in the file in the MM/DD/YYYY format.

Problem:

I have given the format mask of those particular columns in the apex page as MM/DD/YYYY but the output after loading the file is populating as 10/13/0025 instead of 10/13/2025.

I have tried in below ways:

1. Here the requirement is to truncate and load each time, when creating data load definition, i have given the target as the tmp table and given the transformation rules for the three columns as below:

BEGIN RETURN TO_DATE(:ACCESSREFERENCEDATE, 'MM/DD/YYYY');

END; I have done the above for other two columns as well, then i added the below code as process to delete and insert in the apex.

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''AMERICAN''';

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA''';

-- Truncate the final table DELETE FROM s_users;

INSERT INTO S_USERS ( ACTIVE,EMPLOYEEID,ACCESSREFERENCEDATE,ACCESSSTARTDATE,ACCESSENDDATE)

SELECT ACTIVE,EMPLOYEEID,ACCESSREFERENCEDATE,ACCESSSTARTDATE,ACCESSENDDATE FROM s_users_TMP; COMMIT; END;

In oracle apex page, for the respective columns, given the format mask : MM/DD/YYYY

2. I have deleted the transformation rules in the data loading, given the data type as varchar in the tmp table for the three columns,

in the process to delete and insert :

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''AMERICAN''';

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA''';

-- Truncate the final table DELETE FROM s_users;

INSERT INTO S_USERS ( ACTIVE,EMPLOYEEID,ACCESSREFERENCEDATE,ACCESSSTARTDATE,ACCESSENDDATE)

SELECT ACTIVE,EMPLOYEEID,

CASE WHEN TRIM(ACCESSREFERENCEDATE) IS NOT NULL
THEN TO_DATE(TRIM(ACCESSREFERENCEDATE), 'MM/DD/YYYY') END,

FROM se_admin_users_TMP;
    COMMIT;END;

then in teh apex page, for every date column, given as MM/DD/YYYY format.

Can you please guide to get the date field in the format MM/DD/YYYY.

Apex version : 20.2.0.00.20