Skip to Content
Former Member
Nov 03, 2010 at 04:27 PM

Converting datetime string containing AM/PM rather than HH24


I'm brand new to Data Integrator and have what I think is a simple and common challenge. I'm turning to this forum because the online documentation didn't address it, nor did searching this forum.

So here's the challenge. I have a text file that contains a column of date/time data of the format MM/DD/YYYY HH:MI:SS AM/PM (note, the hour is not HH24), for example: '11/03/2010 12:25:00 PM'. I'd like to load this directly into a a DATE (date/time) column in an Oracle table. I can't figure out how to do this as part of the File Format Editor. I also can't get the to_date function to work as part of a Query.

The documentation for to_date simply says to use 'HH24' to load the 2-digit hour of the day (0-23).

I tried to_char( [my column], 'MM/DD/YYYY HH:MI:SS AM') but that failed on all the PM dates. Clearly (unlike the Oracle TO_DATE function) it's not treating the 'AM' in my format parameter as "look for AM or PM and treat it accordingly."

There's got to be a built-in solution for parsing date/time strings that include AM or PM, right? If not please advise.

Thanks in advance,