Question Re: date format in oracle database in Technology Q&A

date format in oracle database

sahana_ps — Thu, 05 Apr 2012 08:39:44 GMT

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

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

Re: date format in oracle database

Former Member — Thu, 05 Apr 2012 08:50:43 GMT

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

Re: date format in oracle database

Former Member — Thu, 05 Apr 2012 09:45:09 GMT

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

Re: date format in oracle database

Former Member — Thu, 05 Apr 2012 09:46:26 GMT

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:

Re: date format in oracle database

sahana_ps — Thu, 05 Apr 2012 11:52:43 GMT

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

mm/dd/yyyy hh:mm:ss pm

Can you Please provide logic for this

thanks,

sahana

Re: date format in oracle database

Former Member — Thu, 05 Apr 2012 11:53:51 GMT

Just use my UDF above.

Regards,
Greg

Re: date format in oracle database

Former Member — Thu, 05 Apr 2012 11:58:39 GMT

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%

Re: date format in oracle database

sahana_ps — Thu, 05 Apr 2012 15:27:55 GMT

hi bhavanisankar,

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

thanks,

sahana

Re: date format in oracle database

baskar_gopalakrishnan2 — Thu, 05 Apr 2012 15:46:33 GMT

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);

Re: date format in oracle database

Former Member — Fri, 06 Apr 2012 05:06:59 GMT

hi sahana,

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

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.

Re: date format in oracle database

sahana_ps — Mon, 09 Apr 2012 09:41:23 GMT

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

Re: date format in oracle database

Former Member — Mon, 09 Apr 2012 09:51:03 GMT

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

Re: date format in oracle database

Former Member — Mon, 09 Apr 2012 10:18:13 GMT

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

Re: date format in oracle database

sahana_ps — Mon, 09 Apr 2012 11:13:15 GMT

hi,

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

mm/dd/yyyy hh:mm:ss am

mm/dd/yyyy hh:mm:ss pm

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

Re: date format in oracle database

Former Member — Mon, 09 Apr 2012 11:25:21 GMT

hi ...

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

Re: date format in oracle database

sahana_ps — Mon, 09 Apr 2012 11:29:49 GMT

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

Re: date format in oracle database

sahana_ps — Mon, 09 Apr 2012 12:32:33 GMT

this is the insert query in communication channel

Re: date format in oracle database

Former Member — Mon, 09 Apr 2012 12:41:04 GMT

refer below forums:

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

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

Re: date format in oracle database

sahana_ps — Mon, 09 Apr 2012 18:01:36 GMT

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

Re: date format in oracle database

Former Member — Mon, 09 Apr 2012 20:33:41 GMT

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