How to convert time stored in varchar2 to 24-hour format in Oracle?

Question

I have time as HH:MI:SS AM/PM stored in a varchar2 column in a table. How can I convert this to 24-hour format?

Solution

To convert to a DATE:

to_date(<text field>, 'DD/MM/YYYY HH:MI:SS AM')

To convert to another string:

to_char(to_date(<date field>, 'DD/MM/YYYY HH:MI:SS AM'), 'DD/MM/YYYY HH24:MI:SS')

e.g. (with NLS_DATE_FORMAT set to YYYY-MM-DD HH24:MI:SS):

select to_date('09/08/2013 5:13:07 PM', 'DD/MM/YYYY HH:MI:SS AM'),
  to_char(to_date('09/08/2013 5:13:07 PM', 'DD/MM/YYYY HH:MI:SS AM'),
    'DD/MM/YYYY HH24:MI:SS')
from dual;

TO_DATE('09/08/2013 TO_CHAR(TO_DATE('09
------------------- -------------------
2013-08-09 17:13:07 09/08/2013 17:13:07

If you only have the time portion:

select to_date('5:13:07 PM', 'HH:MI:SS AM'),
  to_char(to_date('5:13:07 PM', 'HH:MI:SS AM'), 'HH24:MI:SS')
from dual;

TO_DATE('5:13:07PM' TO_CHAR(
------------------- --------
2013-08-01 17:13:07 17:13:07

Notice that if you don't provide the date part of the value it defaults to the first day of the current month (mentioned in the documentation for datetime literals; but if you only have the time you probably want to keep it as a string anyway.

Comments

[t-clausen.dk]

seriously, if you have a date in a varchar2, isn''t it time to store it in a datetime2 column?

[MontyPython]

Out of my hands. I don't have the access. I am more annoyed than you are but this is what I have to work with.