PLSQL Convert String to Datetime

Question

The 'closed_date' column and 'submit_date' are loaded into Oracle as strings, they look like this:

8/17/2017 12:41 (in 24hrs)

How can I convert this string format into date in the format of mm/dd/yyyy hh24:mi:ss

Thank you!

Something is very wrong with your question. If the input string is too short (for example it is missing seconds where the date format expects it) you would get a very different error: ORA-01840: input value not long enough for date format. The error you shared, ORA-01830: date format picture ends before converting entire input string, means just the opposite: your input string is too long and the format doesn't have enough elements to convert it. So something in what you told us is not true. Please research it and then edit your post to clarify. — mathguy

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-08-31T16:32:32

Given your date format, you don't want seconds:

select to_date(CLOSED_DATE, 'mm/dd/yyyy hh24:mi') as CLOSED_DATE,
       to_date(SUBMIT_DATE, 'mm/dd/yyyy hh24:mi') as SUBMIT_DATE
from s_daily_ops