How to convert date : Sep 26 00:15:00 2020 in YYYY/MM/DD HH24:MI:SS’ format in oracle sql

Error while converting

select to_date('Sep 26 00:15:00','DD/MM/YYYY HH24:MI:SS') from dual ;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 .

Looking for solutions

Answer

With TO_DATE(), you want to convert a string – 'Sep 26 00:15:00' to an Oracle DATE type – that is a type that counts, internally, the number of seconds since an epoch date.

In Unix, that would be ‘1970-01-01 00:00:00’, in some other databases I know ‘2000-01-01 00:00:00’. I don’t know about Oracle.

So you convert the string: 'Sep 26 00:15:00'. That is: the three-letter English month name abbreviation, a space, the day as two digits, a space, two digits for the 24hour-notated hour, a colon, two digits for the minutes, another colon, and two digits for the second. Nothing for the year, to be precise. The second parameter of TO_DATE() must describe that format. It would therefore be: 'Mon DD HH24:MI:SS' .

The second parameter you use makes absolutely no sense in this context. You could, if you want to play, use it to re-format the converted DATE type, back to a string, in the ISO format, using TO_CHAR():

SELECT 
  TO_CHAR(
    TO_DATE('Sep 26 00:15:00','Mon DD HH24:MI:SS')
  , 'YYYY-MM-DD HH24:MI:SS'
  )
FROM dual;