My customer has loaded a TD test table with a date & time column that looks like this:
Column: XXXXXX varchar(23)
7/31/2014 9\:20\:03 AM
8/4/2014 8\:23\:15 AM
12/6/2014 2\:31\:07 PM
What is the quickest/easiest way to convert the data to TIMESTAMP(6), if possible at all ?
The output should look something like YYYY-MM-DD HH:MM:SS.ssssss
what's your TD release?
In TD14 you might utilize Oracle's TO_TIMESTAMP, which is a bit more flexilbe regarding single digit day/hour/minute/second, but month still needs to be two digits. But quoted characters like "\" seem to be allowed only in TO_CHAR.
So add the missing zero, remove the backslash and pass it to TO_TIMESTAMP:
TO_TIMESTAMP(OREPLACE(CASE WHEN x LIKE '_/%' THEN '0' ELSE '' END || x, '\',''), 'mm/dd/yyyy hh:mi:ss AM')