UDF for Date time conversion

[former_member255627]

Hi All,

I have a requirement to write the udf for date time conversion.The input date time format 2015-02-15T08:00:00Z needs to converted into 201502150800.

Please suggest.

Thanks in advance.

[engswee · Accepted solution]

Hi Pushpalatha

I think you should be able to achieve this using the standard mapping function DateTrans. Just need to enter a format that conforms to the format allowed in SimpleDateFormat. T is not one of the allowed characters to need to be encapsulated in single quote 'T'.

Input format: yyyy-MM-dd'T'HH:mm:ssZ

Output format: yyyyMMddHHmmss

Rgds

Eng Swee

[engswee · Accepted solution]

By the way, your input value doesn't seem correct, the Z at the end needs to be an actual value (PST, -0800, etc) as the format below, and not an actual Z character.

Refer example below where I used the Z format for input (with -0700 in input value) and the converted value as MYT because of z format in output.

[former_member255627 · Accepted solution]

Hi Eng Swee,

Thanks for your quick reply.

I rechecked the input date from source side and it is the same format(2015-02-15T08:00:00Z) they are sending.

I tried with graphical mapping as suggested by you but no luck.

Could you help me writing the udf for the same.

[engswee · Accepted solution]

If you don't need the timezone details, then I suggest that you use a standard function replaceString to replace the Z with a no-space blank (so that Z is removed) before the value is then passed to the DateTransform function.

This can all be easily achieved with standard functions, having a UDF only unnecessarily complicates your design. Even if you use UDF, the standard Java methods also uses SimpleDateFormat and therefore cannot accept the literal Z.

[engswee · Accepted solution]

If you go for the replaceString approach as I suggested above, please also remember to remove Z from the configuration of the DateTrans function. It should look like below

Input format: yyyy-MM-dd'T'HH:mm:ss

Output format: yyyyMMddHHmmss

[former_member255627 · Accepted solution]

Hi Eng Swee,

Thanks a lot. I am able to get the desired output with your suggestion.

[engswee · Accepted solution]

Great, good to hear that

[giridhar_vegi]

Hi pushpa please checkthis

Thanks

Giridhar

[naveen_chichili]

HI Pushpa,

You can use date trans (standard function) as suggested by Eng Swee.

Thanks and Regards,

Naveen

[Former Member]

Hi Puspalatha,

Try this code.

Code

//write your code here

   //The Input timestring is yyyymmddHHMMSS

int l_year,l_month,l_day,l_hour,l_minute,l_second;

int t_year,t_month,t_day,t_hour,t_minute,t_second;

String retTimeStr = "";

try{

            l_year   = Integer.parseInt(inTimeStr.substring(0,4));

            l_month  = Integer.parseInt(inTimeStr.substring(4,6));

            l_day    = Integer.parseInt(inTimeStr.substring(6,8));

            l_hour   = Integer.parseInt(inTimeStr.substring(8,10));

            l_minute = Integer.parseInt(inTimeStr.substring(10,12));

            l_second = Integer.parseInt(inTimeStr.substring(12,14));

            //Get Local Time Object

            TimeZone tz = TimeZone.getTimeZone(inSrcTimeZone);

            Calendar local = new GregorianCalendar(tz);

            local.set(l_year,l_month,l_day,l_hour,l_minute,l_second);

            //Change to target Time Object

            tz = TimeZone.getTimeZone(inTgtTimeZone);

            Calendar target = new GregorianCalendar(tz);

            target.setTimeInMillis(local.getTimeInMillis());

            //Format Output

            t_year   = target.get(Calendar.YEAR);

            t_month  = target.get(Calendar.MONTH);

            t_day    = target.get(Calendar.DATE);

            t_hour   = target.get(Calendar.HOUR);

            t_minute = target.get(Calendar.MINUTE);

            t_second = target.get(Calendar.SECOND);

            retTimeStr = ""+t_year+"-";

            if(t_month < 10)

                        retTimeStr+="0"+t_month+"-";

            else

                        retTimeStr+=t_month+"-";

            if(t_day < 10)

                          retTimeStr+="0"+t_day+" ";

            else

                          retTimeStr+=t_day+" ";

            if(t_hour < 10)

retTimeStr+="0"+t_hour+":";

            else

                          retTimeStr+=t_hour+":";

            if(t_minute < 10)

retTimeStr+="0"+t_minute+":";

            else

                          retTimeStr+=t_minute+":";

            if(t_second < 10)

                          retTimeStr+="0"+t_second;

            else

                          retTimeStr+=t_second;

}catch(Exception e){

            return e.toString();

}

return retTimeStr;

Regards,

-Partha