CODEWITHSUNDEEP
hadoophive
Alka
Alka

Reputation: 267

Convert date string in “MM/DD/YY” format

I just saw this example.How can I get this solved.

The Hive metastore contains a database named problem1 that contains a table named customer. The customertable contains 90 million customer records (90,000,000), each with a birthday field.

Sample Data (birthday is in bold)

1904287 Christopher Rodriguez   Jan 11, 2003
96391595    Thomas Stewart      6/17/1969
2236067 John Nelson             08/22/54

For every row in the solution table, replace the contents of the birthday field with a date string in “MM/DD/YY” format.

MM is the zero-padded month (01-12),
DD is the zero-padded day (01-31),
YY is the zero-padded 2-digit year (00-99) 

select from_unixtime(unix_timestamp(doj,'MM/dd/yyyy')) from temp1;

Upvotes: 3

Views: 863

Answers (1)

cheseaux
cheseaux

Reputation: 5315

You can't do it in a generic way, you'll have to test each possible case. You can do it like this 

select coalesce(
    from_unixtime(unix_timestamp(doj ,"MMM dd, yyyy"), "MM/dd/yy"),
    from_unixtime(unix_timestamp(doj ,"MM/dd/yyyy"), "MM/dd/yy"),
    from_unixtime(unix_timestamp(doj ,"MM/dd/yy"), "MM/dd/yy")
)

Upvotes: 1

Related Questions