How to convert Julian Date to Calander Date in Hive?

Most of the Legacy Systems store the date in Julian Format. I came across a problem converting this to Calander date in Hive.  Please see the solution below.

As long as the date stored in yyyyDDD format this should work.

Refer the date here, it’s matchig.

Solution in Hive:

hive> select report_date,substr(from_unixtime(unix_timestamp(cast(cast(report_date as int) as string),’yyyyDDD‘)),1,10) from datetable limit 20;
2012142       2012-05-21
2012101       2012-04-10
2012042       2012-02-11
2012004       2012-01-04
2011299       2011-10-26
2011251       2011-09-08
2011145       2011-05-25
2011021       2011-01-21
2014076       2014-03-17

Time taken: 0.251 seconds, Fetched:

Java Code for this:

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class JulianTest
    public static void main(String args[]) throws ParseException {
        String julianDt = "2014076";
        //Declare the date format,here yyyyDDD - The julian
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyDDD");
        SimpleDateFormat sdfIso = new SimpleDateFormat("yyyy-MM-dd");
        Date dt = sdf.parse(julianDt);
        System.out.println("Julian Date: " + julianDt + " in ISO date format :" + sdfIso.format(dt));

Jave Output:

Julian Date: 2014076 in ISO date format :2014-03-17

Process finished with exit code 0

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s