Oracle Get only time from To_Date() in a Query?

Question

I have an insert query:

INSERT INTO THE_TABLE (Field_A) 
VALUES (TO_DATE('08/12/2011 08:35:42 AM','HH:MI:SS PM'))
WHERE Something = 'SomethingElse'

Field_A is a Date field. When I execute the query, Field_A still shows a date and time.

Is there a way to grab only the time?

I have tried To_Char(), but then it converts to a string and the field wont take it.

I have also tried TO_DATE('08:35:42 AM','HH:MI:SS PM') and that doesn't work either, it still shows a date and time in the table.

Answer 1

TO_DATE('08:35:42 AM','HH:MI:SS PM')

The reason this doesn't work is because this not a complete date. Even when you use a to_date('07/12/2011','MM/DD/YYYY'), Oracle stores the date and time, but makes all the components of the time ZERO. So the actual date stored is 07/12/2011 HH:MI:SS

If you want to store the time component seperately, It should be a varchar field and you'll need to add it to the date part to get the complete date. Example..

Select to_date(
          to_char(date_field_stored_as_date,'DD-MON-YYYY') || 
          ' ' ||
          to_char(time_field_stored_as_varchar),
         'DD-MON-YYYY HH24:MI:SS'    
       )