Hive: Filtering Data between Specified Dates when Date is a String

date, filter, hive

Solution

The great thing about `yyyy-mm-dd` date format is that there is no need to extract `month()` and `year()`, you can do comparisons directly on strings:

SELECT *
  FROM your_table
  WHERE your_date_column >= '2010-09-01' AND your_date_column <= '2013-08-31';

Problem

I'm trying to filter data between September 1st, 2010 and August 31st, 2013 in a Hive table. The column containing the date is in string format (yyyy-mm-dd). I can use month() and year() on this column. But how do I use them to filter data between the above dates? Any examples/sample code would be welcome!

Original source