oracle的dateformat,Oracle - Date Format

This query

select TO_DATE('20150731 00:00:34.550','YYYYMMDD')

from dual

leads to error

ORA-01830: date format picture ends before converting entire input string

because you pass string with length 22 characters, but at the same time you pass date format with 8 characters, which obviously doesn't correspond to string. You should write the query as

select to_timestamp('20150731 00:00:34.550','yyyymmdd hh24:mi:ss.ff3')

from dual

As for your table, since you have varchar2 column with dates, you have to take care about table content. Query requires exect matching of the source string and date format.

If you want to show only date without time and you don't need to process this string as date, you can make just

select substr('20150731 00:00:34.550', 1, 8)

from dual