All Forums Database
OM186000 1 post Joined 07/12
12 Aug 2014
Varchar to Timestamp(6) conversion

My customer has loaded a TD test table with a date & time column that looks like this:
Column:      XXXXXX   varchar(23) 
Data:
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   
 
Thanks
Orlando

-- Thanks, Orlando

dnoeth 4628 posts Joined 11/04
15 Aug 2014

Hi Orlando,
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')

 

Dieter

You must sign in to leave a comment.