Hive UDF String to Date Convertion

Question

The Hive contains a table "sample" with the following data:

15-06-2015
15-06-2015
15-06-2015
15-06-2015
15-06-2015
15-06-2015
16-06-2015

Using the following query i am converting String type of data into Hive Date format:

select TO_DATE(from_unixtime(UNIX_TIMESTAMP(date,'DD-MM-YYYY'))) from sample;

The output as follows:

0   2014-12-28
1   2014-12-28
2   2014-12-28
3   2014-12-28
4   2014-12-28
5   2014-12-28
6   2014-12-28

The output is differ from expected output.

Please suggest how to get the expected output like:

2015-06-15
2015-06-15
2015-06-15
2015-06-15
2015-06-15
2015-06-15
2015-06-16

Answer 1

Your problem is that HiveQL is generally case insensitive, but the second argument of the unix_timestamp function, instead, is case sensitive. Thus the right syntax for your query is:

select TO_DATE(from_unixtime(UNIX_TIMESTAMP(date,'dd-MM-yyyy'))) from sample;

In this way, you'll get the expected result.