date format in oracle database

[sahana_ps]

hi all,

I have to insert date and time to oracle db from pi and it has a format

mm/dd/yyyy hh:mm:ss am

or

mm/dd/yyyy hh:mm:ss pm

how can i achieve this in PI .how can i get time in 12 hr format...

thanks,

sahana

[sahana_ps]

thanks all,i solved the issue...

no need of udf or complex mapping

it can be achieved in TO_DATE() function  passing current date as MM/dd/yyyy HH:mm:ss a

[Former Member]

and you can also use the copyValue finction instead of useOneasMany make it less complicated .

[sahana_ps]

this is the insert query in communication channel

[Former Member]

refer below forums:

http://scn.sap.com/thread/1038542

http://scn.sap.com/thread/148535

[Former Member]

u ll try below logic it's working perfect because i was worked same logic in one interface

that is very simple by using standard functions like substring and concat.

ex:

[sahana_ps]

hi

i need exactly opposite of ur logic

my i/p will be:

mm/dd/yyyy hh:mm:ss

o/p should be:

mm/dd/yyyy hh:mm:ss am

or

mm/dd/yyyy hh:mm:ss pm

Can you Please provide logic for this

thanks,

sahana

[former_member184681]

Just use my UDF above.

Regards,
Greg

[Former Member]

hi sahana ,sorry  Mistakenly I wrote example is reverse order first one is INput and second one is outputb in my pic   ,

that logic should be working 100%

[sahana_ps]

hi bhavanisankar,

there is something wrong in your logic,its not working ..can you just check it again

thanks,

sahana

[baskar_gopalakrishnan2]

You can try UDF with few lines of coding. This is another simpler approach.

Note: Create method with return String type and pass your sender date as input parameter

String input = "April 5, 2012 1:14 AM";

DateFormat df = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a");

Date date;

// Parse the date

try {

             date = DateFormat.getDateTimeInstance(DateFormat.MEDIUM, DateFormat.SHORT).parse(input);

  } catch(ParseException pe) {

            pe.getStackTrace();

  }

  return df.format(date);

[Former Member]

hi sahana,

ur

i/p will be:

mm/dd/yyyy hh:mm:ss ->   05/04/20112 23:57:47,06/04/2012 00:01:15

o/p should be:

mm/dd/yyyy hh:mm:ss am -> 06/04/2012 00:01:15 AM

or

mm/dd/yyyy hh:mm:ss pm -> 05/04/20112 11:57:47 PM

is it correct?

if correct , once use below logic :

in currecnt date , u need to split the value by using substring(11,2) means hours only if that value is greather than 12 then subtract the value with 12 then concat total string , else pass the same value with concat spaceAM  thats all.

[sahana_ps]

hi ,

your logic is working fine but since i am using currentDate function this wil insert date in only first record...this date wil not get inserted in other records ...

Any help in solving this?.....

thnaks,

sahana

[Former Member]

Hi,

If you want to insert the same date in multiple target records then you can use "use one as many" function...

Thannks

Amit Srivastava

[Former Member]

if you want to use same date value in mutipl times in target structure, we need to use "useOne AsMany" funtion of the Node Function group and u cen use before target field .

ref below link for ex  how to use standard function "useOne AsMany" :

http://www.saptechnical.com/Tutorials/XI/NodeFunctions/Page5.htm

Message was edited by: solasu bhavanisankar

[sahana_ps]

hi,

Now i can see date getting created in every record in the format

mm/dd/yyyy hh:mm:ss am

or

mm/dd/yyyy hh:mm:ss pm

but still i am getting error: ORA-1843 :not a valid month

[Former Member]

hi ...

You entered a date, but the month portion of the date was not a valid month... once you check the month...

[sahana_ps]

hi,

i can see  from communication channel monitoring insert statement generated

with date as:

04/09/2012 05:23:14 AM

this looks like a valid month

[Former Member]

Dear Sahana

Date trans should work.

Input should be yyyyMMdd hh.mm.ss if you are using a proxy

Output dd-MMM-yy hh.mm.ss

Regards

Monika

[former_member184681]

Hi,

Try with this little UDF (assuming that input is your input variable):

Date date = DateFormat.parse(input);
DateFormat df = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a"); 
return df.format(date);

Hope this helps,
Greg