conversion from varchar to datetime

dataservices sap-erp-bw
1

Hi to all,

In my DI Job the source is Text file . On that we have one datetime field column (Ex. 01/22/2011 09:11:06 PM ) .

Thru DI , i am able to load the data into Oracle, i use the date conversion in DI for that field as

to_date(field colum, ‘mm/dd/yyy hh:mi:ss’) as datetime .

But the output is getting as : 01/22/2011 09:11:06 AM

Instead of PM or AM in source the output is getting as opposite ie, AM or PM . Eventhough i gave the format mm/dd/yyyy hh24:mi:ss too…

Can anyone Please help me on this issue.

Thanks in Advance … Sureshchowdary

vasanthasuresh (BOB member since 2011-07-28)

2

The format and the value does not match

01/22/2011 09:11:06 PM
mm/dd/yyyy hh:mi:ss

Your value has a PM after, the format string does not specify it.

Werner Daehn :de: (BOB member since 2004-12-17)

3

Can u plz help me for the format how can i write?

Thanks in advance

vasanthasuresh (BOB member since 2011-07-28)

4

I was just about the say you should read the manual when I looked it up myself and…nothing. Checked with development, it seems we do not support AM/PM notation!?!

Werner Daehn :de: (BOB member since 2004-12-17)

5

That is simply lying crazy for a long time :reallymad:

ganeshxp :us: (BOB member since 2008-07-17)

6

Know what development said?

They said: :shock:

Werner Daehn :de: (BOB member since 2004-12-17)

7

I recently wrote a function that formats the text time of AM or PM into military time. The function looked easy to write but it took several hours…

:oops:
timefunction.zip (1.0 KB)

srinathpedi (BOB member since 2007-09-25)

8

Would you please share the Text version of that? That would be awesome. It is really great to see the help

ganeshxp :us: (BOB member since 2008-07-17)

9

Text version will be difficult to use… Still if you want here you go…

$InitTimeString VARCHAR(12)

RETURNS TIME

#It converts the time of the format 12:00AM or 11:59AM etc…

$InitTimeString =
cast(
cast(
ifthenelse(
substr( $Ava_Milit_Time,7,2)
=‘PM’,12,0
)
,‘int’
)
+
ifthenelse(
cast(
substr($Ava_Milit_Time,1,2)
,‘int’
)
=12,0,cast(
substr($Ava_Milit_Time,1,2)
,‘int’
)
)
,‘varchar(2)’
)

||

cast(
substr( $Ava_Milit_Time,3,3)
,‘varchar(3)’
)
;

return to_date($InitTimeString,‘HH24:MI’);

srinathpedi (BOB member since 2007-09-25)