cancel
Showing results for 
Search instead for 
Did you mean: 

How to split the column

Bhargavakrishna
Active Contributor
0 Kudos

Hi,

We have a Column called Event_Date in SQL server 2008. The data is recording in this format YYYY-MM-DD HH:MM:SS:MS.  i want the data in such away that, the date should be in one column and the Time should be in another column. The Date should be saved in this format.

Date: DD-MM-YYYY

Time: HH:MM

Can someone guide me how to achieve this?

Accepted Solutions (1)

Accepted Solutions (1)

former_member184789
Active Contributor
0 Kudos

Hi, first separate YYYY-MM-DD from HH:MM:SS:MS by having substring (0,9) for YYYY-MM-DD & (10,14) for HH:MM:SS:MS since you only need HH:MM. Map the subtsing (10,14) directly to the field to store HH:MM & for the date field after substring(0,9) apply the date function to change the input date format of YYYY-MM-DD into DD-MM-YYYY

Bhargavakrishna
Active Contributor
0 Kudos

Thank you Adarsh.. my issue resolved:)

Bhargavakrishna
Active Contributor
0 Kudos

Thank you all.. my issue resolved..

former_member184789
Active Contributor
0 Kudos

You are welcome

Answers (2)

Answers (2)

iaki_vila
Active Contributor
0 Kudos

Hi Bhargava,

You could use the standard function DateTrans, in this way,

For the date:

For the time:

Regards.

Bhargavakrishna
Active Contributor
0 Kudos

Hi, can we use the Same filed multiple times in message mapping?

My sender field will be Event_Date: 2013-03-23 15:24:56:45

i want this data at the target side should be saved as  Date: 23-03-2013

                                                                               Time: 15:24.

Former Member
0 Kudos

Hi ,

Use the below udf.Pass input date ,Inputdate format and output date format as inputs.

String outputDate;

SimpleDateFormat SDFInput = new SimpleDateFormat(inputDateFormat[0]);

SimpleDateFormat SDFOutputDate = new SimpleDateFormat(outputDateFormat[0]);

if(inputDate[0]!="") {

if(inputDate[0].length()==inputDateFormat[0].length()){

try{

                                        Date date = SDFInput.parse(inputDate[0]);

                                        outputDate.addValue(SDFOutputDate.format(date));

                                     

//Split ur date and time and pass it to output

                                }catch(Exception e) //It is up to you

                                {}

Regards

Venkat

Bhargavakrishna
Active Contributor
0 Kudos

Hi Venkat, thank you very much for your reply. i don't have any experience in UDF. so can you guide me how to import those in PI.

Can we achieve this with the standard functions available in Message mapping rather than going for UDF?