I have a strange date format in AS400, that I need to convert to an Oracle date format (i.e dd-mm-yyyy).Below are dates which is stored in AS400.
101
820101
820101
820101
820101
820101
820101
820101
10728
820101
820101
820101
820101
Maybe your date are stored as some numeric data type? Simply left-pad your data with 0 before converting the corresponding string to date would solve your issue.
So if you need to convert your data to the DATE type, you will write:
CREATE TABLE T2 AS
SELECT TO_DATE(LPAD("AS_DATE", 6, '0'), 'RRMMDD') ORACLE_DATE FROM T;
-- ^^^^^^^^^^^^^^^^^^^^^^
-- left pad missing '0'
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- convert to date with the correct format (I assume)
Please note the RR format that will take care to map the 2-digits years in the range 1950 to 2049
Even if I can't encourrage this, if for some reason you need to store your date as strings with the format dd-mm-yyyy, you will write something like that:
CREATE TABLE T3 AS
SELECT TO_CHAR(TO_DATE(LPAD("AS_DATE", 6, '0'), 'RRMMDD'), 'DD-MM-YYYY') STRING_DATE FROM T;