Invalid number when converting a date to another format

I have this query, and i am trying to format all dates for the two columns mentioned in the query below to the format mentioned:

SELECT
    TO_DATE(TO_CHAR(timeone,'YYYY-MM-DD HH:MI PM'),'DD-MM-YYYY HH:MIPM'), 
    TO_DATE(TO_CHAR(timetwo,'YYYY-MM-DD HH:MI PM'),'DD-MM-YYYY HH:MIPM')
FROM
    my_date_table
WHERE daywork = '28-MAY-2021';

and i want to convert the results from timeone and timetwo to another date format DD-MM-YYYY HH:MIPM but when I run the query I got back invalid number. The datatype of the timeone and timetwo columns in my table are date

When i run the query without the formatting, the results are:

Where did i go wrong?. All alternatives and solutions are welcomed

Answer

The datatype of the timeone and timetwo columns in my table are date

The error says that isn’t the case; they seem to be strings. As a date this would work:

SELECT
    TO_CHAR(CAST(TIMESTAMP '2021-05-28 07:34:00' AS DATE),'YYYY-MM-DD HH:MI PM')
FROM
    dual

TO_CHAR(CAST(TIMESTAMP'2021-05-2807:34:00'ASDATE),'YYYY-MM-DDHH:MIPM')
----------------------------------------------------------------------
2021-05-28 07:34 AM

but as a string it would get the error you see:

SELECT
    TO_CHAR('2021-05-28 07:34:00','YYYY-MM-DD HH:MI PM')
FROM
    dual

ORA-01722: invalid number

So, you are really trying to convert one string representation of a date to a differently-formatted string representation. To do that you need the inner function call to be to_date(), and the outer function call to be to_char() – you have yours the wrong way around:

SELECT
    TO_CHAR(TO_DATE(timeone,'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YYYY HH:MIPM') as convertedone, 
    TO_CHAR(TO_DATE(timetwo,'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YYYY HH:MIPM') as convertedtwo
FROM
    my_date_table
WHERE daywork = date '2021-05-28'

CONVERTEDONE       CONVERTEDTWO
------------------ ------------------
28-05-2021 07:34AM 28-05-2021 03:30PM
28-05-2021 07:04AM 28-05-2021 07:04AM

The format model for the to-date() call also has to match the actual string value, so I’ve changed that to 'YYYY-MM-DD HH24:MI:SS'. And '28-MAY-2021' is also a string not a date, so – assuming that really is a date field – I’ve changed that to a date literal. You could also use a to_date() on that string value, but it’s easier with a literal (and if you supply a month name then you should specify the date language too).

db<>fiddle.