Sunday, 21 April 2013

How to convert to any date format in Talend (Java)


Lot of time we need to convert date formats from one to another based on requirements of downstream applications or system requirements. Hence, I have create a simple function which will help to transform date formats of a date value in string.

You can add this function to your existing routine in a project or create new routine. Click here for more details on Creating Routines in Talend.

Input parameters of functions:

1. String dateString - Its the actual date string which we want to convert.
2. String sourceDateFormat - Format of the date which needs to be converted.
3. String sourceDateFormat - Target date format.



convertAnyDateFormat("04-20-2013 13:00:00", "MM-dd-yyyy HH:mm:ss", "yyyy-MM-dd") 
Result: 2013-04-20

convertAnyDateFormat("20-04-2013 03:00:00 AM", "dd-MM-yyyy hh:mm:ss z", "yyyy-MM-dd") 
Result: 2013-04-20

convertAnyDateFormat("20-04-2013 03:00:00 AM", "dd-MM-yyyy hh:mm:ss z", "hh:mm:ss z") 
Result: 03:00:00 AM

+David Green has helped me to identify that we can also convert Julian dates as well

convertAnyDateFormat("2012032", "yyyyddd", "yyyy-MM-dd")
Result 2012-02-01

convertAnyDateFormat("2012065", "yyyyddd", "yyyy-MM-dd")
Result 2012-03-05

convertAnyDateFormat("2012365", "yyyyddd", "yyyy-MM-dd")
Result 2012-12-30


You may also like to read..

6 comments:

  1. Thanks for the routine; it would be nice if you add some text about SimpleDateFormat, like what it does, or why you chose to use it and what the processing cost is to use it.

    Thanks again,
    dg

    ReplyDelete
  2. so putting this code into your routine structure for adding routines would look something like this?

    package routines;

    import java.text.SimpleDateFormat;
    import java.text.ParseException;

    public class convertAnyDateFormat {

    public static String convertDateFormat(String dateString,String sourceDateFormat,String targetDateFormat)
    {
    SimpleDateFormat sdf_source = new SimpleDateFormat(sourceDateFormat); // Define instance of Source format

    SimpleDateFormat sdf_target = new SimpleDateFormat(targetDateFormat); // Define instance of Target format

    String var_Date = "";

    try
    {
    var_Date = sdf_target.format(sdf_source.parse(dateString));
    }
    catch(ParseException e)
    {
    System.out.println("Source Format not correct");
    }

    return var_Date;

    }
    }

    This seems to compile cleanly. Do you have a reference to the date parts that may be used? I work with JDEdwards mostly, so I would be interested in converting julian type dates. The JDE date looks like CYYDDD where C=0 for 1900 and 1 for 2000, YY is the year, DDD is the day from 1 to 366. Would these date parts be found in SimpleDateFormat? If not, I already have a formula to convert, I just was wondering.

    ReplyDelete
  3. Yes, you are right. Julian dates does not seem to be handled by SimpleDateFormat. If you already have formule, I and readers will be if you share the same.

    ReplyDelete
  4. Vikram, I found the list of date parts on Oracle website for SimpleDateFormat. They supply parts of the normal Julian data, yyyDDD or yyDDD will give Year (four or two digit) and then day of the year. To convert to JDE date format (CyyDDD) I have to take yyyyDDD format, convert to Integer and subtract 1900000; for example:

    1998360 - 1900000 = 098360 (Century 0, year 98, 360 day of year)
    2013123 - 1900000 = 113123 (Century 1, year 13, 123 day of year)

    To convert from JDE date to any other date, I can take the integer date from JDE and add 1900000, convert to string and use convertAnyDateFormat to change from "yyyyDDD" to something like "MMM dd, yyyy" or any other normal format.

    Now I would only have to worry about handling a JDE date equal to 0 (no date) or a date field that contains null (no date), both which are valid considerations.

    Thanks for your Blog!
    dg

    ReplyDelete
  5. By the way, my routine for converting a JDE date in SQL to a real date field is simply a case statement:

    CASE JDEDATE
    WHEN 0 THEN NULL
    ELSE DATE('1899-12-31') + INTEGER(JDEDATE * 0.001) YEARS +
    MOD(JDEDATE, 1000) DAYS
    END as "RealDateFormat"

    Since I am not a Java programmer, I'm not sure how to do this in a custom routine.

    ReplyDelete
  6. Hello David,

    My routine converts the Julian dates format as well. You can use the above code mentioned in my original blog posting and convert Julian dates to other formats

    COMMON.convertAnyDateFormat("2012032", "yyyyddd", "yyyy-MM-dd")
    Result 2012-02-01

    COMMON.convertAnyDateFormat("2012065", "yyyyddd", "yyyy-MM-dd")
    Result 2012-03-05
    COMMON.convertAnyDateFormat("2012365", "yyyyddd", "yyyy-MM-dd")
    Result 2012-12-30

    Let me know, if you have any queries.

    ReplyDelete