My table in hive has a filed of date in the format of '2016/06/01'. but i find that it is not in harmory with the format of '2016-06-01'. They can not compare for instance. Both of them are string . So I want to know how to make them in harmory and can compare them. Or on the other hand, how to change the '2016/06/01' to '2016-06-01' so that them can compare.

Many thanks.

  • A good question includes information about what you have tried to solve the problem. This question doesn't show any research effort -- not even Googling "change date format in hive" -- so it really shouldn't attract answerers.
    – Noumenon
    Jun 1 '16 at 3:24
  • 2
    @Noumenon you could have provided the link, cloudera.com/documentation/cdh/5-1-x/Impala/… Oct 12 '17 at 10:16

To convert date string from one format to another you have to use two date function of hive

  1. unix_timestamp(string date, string pattern) convert time string with given pattern to unix time stamp (in seconds), return 0 if fail.
  2. from_unixtime(bigint unixtime[, string format]) converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone.

Using above two function you can achieve your desired result.

The sample input and output can be seen from below image: enter image description here

The final query is

select from_unixtime(unix_timestamp('2016/06/01','yyyy/MM/dd'),'yyyy-MM-dd') from table1; 

where table1 is the table name present in my hive database.

I hope this help you!!!

  • how can this be done without a fixed date, but a date that is already in the db as 20180401? May 30 '18 at 21:09
  • 1
    You just need to write the name of the column instead of the fixed date.
    – nessa.gp
    Aug 27 '19 at 13:26

Let's say you have a column 'birth_day' in your table which is in your format, you should use the following query to convert birth_day into the required format.

date_Format(birth_day, 'yyyy-MM-dd')

You can use it in a query in the following way

select * from yourtable
date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';

Use :

unix_timestamp(DATE_COLUMN, string pattern)

The above command would help convert the date to unix timestamp format which you may format as you want using the Simple Date Function.

Date Function

cast(to_date(from_unixtime(unix_timestamp(yourdate , 'MM-dd-yyyy'))) as date)
  • 5
    Please include an explanation of what your code does and how it solves OP's problem.
    – mypetlion
    Mar 12 '19 at 17:01

here is my solution (for string to real Date type):

select to_date(replace('2000/01/01', '/', '-')) as dt ;

ps:to_date() returns Date type, this feature needs Hive 2.1+; before 2.1, it returns String.

ps2: hive to_date() function or date_format() function , or even cast() function, cannot regonise the 'yyyy/MM/dd' or 'yyyymmdd' format, which I think is so sad, and make me a little crazy.

Your Answer

