Converting datetime string containing AM/PM rather than HH24

[Former Member]

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,

Kirke

[Former Member]

I am not sure on what i am proposing but cant you write something in the where clause in TRANSFORM :

Which says :

1>>

WHEN TIME > 12:00:00

AM

OTherwise PM.

2>> Or you wirte something like

Where ( Read the 13th and 14th Character of your input string and see whats the number if its greater than 12 then make it PM

other wise AM..

I know these both are not good methods but i am also new to DS to try this. If i come across something useful i will post it back.

Regards,

Den

[Former Member]

The following custom function works, but I hope there is a solution that requires no custom coding or if-then logic.

$ampm = (substr($i_datetime,-2,2));

if ($i_datetime is null)

begin

return null;

end

else if ($ampm in ( 'AM', 'PM'))

begin

$hourpart=substr($i_datetime,12,2);

if ($ampm = 'AM' and $hourpart = '12')

begin

$hourpart = '00';

end

else if ($ampm = 'PM' and $hourpart < '12')

begin

$hourpart=lpad(cast($hourpart, 'int')+12,2,'0');

end

return(to_date(substr($i_datetime,1,11) || $hourpart ||substr($i_datetime,14,6),'MM/DD/YYYY HH24:MI:SS'));

end

[Former Member]

Hi Kirke,

Could you not simply load the text data into a staging table as is, and then have the next Data Services transform convert the data to the appropriate datetime format using an ifthenelse and substring function?

Thanks

[Former Member]

I appreciate the suggestion and I think it answers my question in the negative. That is Data Integrator (inexplicably and unfortunately) doesn't provide native functions to parse "normal" times which include AM/PM instead of 24 hour time. I assume it's a function of its European lineage. Oh well, it's not that hard to work around this limiitation.

Thank you.