UDF to convert into datetime format for oracle database

[former_member207622]

HI Folks

I need a UDF which will insert the date in datetimeformat in oracle database via jdbc 

my input is  say -    20120309 ( datab coming from idoc )  concatenated with constant ( 12.00.00 )

my input to udf will be 20120309 12.00.00  and my output should be  09-MAR-12 12.00.00

please help me at the earliest i tried date trans function but not working

Thanks

Ninad

[former_member761281 · Accepted solution]

Hi Ninad.

In DT add attribute hasQuot with default value "No", this for your field DATE

like this:

and also you must apply this function:

TO_DATE('15-02-2009 12:00:00 AM', 'dd-mm-yyyy HH:MI:SS AM') to field "DATE"

I hope help you.

[Former Member]

Hi

u refer below links:

http://www.saptechnical.com/Tips/XI/Date/Oracle.htm

http://scn.sap.com/people/sarvesh.singh3/blog/2009/02/13/yes-it-is-possible-with-datetrans-function

[deepak_shah]

Hi Ninad,

check this link. this could be achieved using mapping itself. UDF is not required.

http://www.saptechnical.com/Tips/XI/Date/Oracle.htm

-Deepak

[former_member361338]

Hi Ninad,

You can try this code -

make sure you import following in the import tab of udf -

java.text.SimpleDateFormat

java.uti.Date ;

sample code -

public String concatDate(String dateIdoc, String time, Container container) throws StreamTransformationException{

String oraDate = "" ;

Date date ;

SimpleDateFormat sdfSource  = new SimpleDateFormat("yyyyMMdd");

SimpleDateFormat sdfDestination = new SimpleDateFormat("dd-MMM-yyyy");

try {

date =  sdfSource.parse(dateIdoc);

}catch (Exception e)

{

     // your exception logic

}

oraDate= sdfDestination.format(date);

oraDate= oraDate+ " " +time ; //String time can be constant as per your requirement or if you get it from idoc you can use it as input to udf

return oraDate;

}

- Piyusha Deshpande

[Former Member]

Dear Ninad

Date trans should work.

Input should be yyyyMMdd hh.mm.ss 

Output dd-MMM-yy hh.mm.ss

Regards

Monika

[former_member184681]

Hi Ninad,

There are few nice formatting examples here:
http://www.exampledepot.com/egs/java.text/formatdate.html

Try the following code in a UDF (assuming that your input parameter is called input😞
Format formatter;
Date date = DateFormat.parse(input);
formatter = new SimpleDateFormat("yy-MMM-dd HH.mm.ss");
String s = formatter.format(date);
return s;

Hope this helps,
Greg

[Former Member]

try this: use datetrans method. input datab to it. double click it - set both input and output date formats. MMM shall give MAR for example. this will output date in req format.

then take time and concatenate it.