Extract year from timestamp in hive

I am writing the query to show the data entries for a specific year. Date is stored in dd/mm/yyyy hh:mm:ss.(Date TIMESTAMP – e.g. 12/2/2014 0:00:00).

I am trying to display the two columns(name, orderdate) filtered by a specific year(year from orderdate). The requirement is to enter the specific year(2010 or 2020 etc) not the entire date. I tried using date_format() and regexp_replace() with WHERE but nothing helped.

Can someone help me?

Answer

If your are storing the date — incorrectly — as a string, then you can use string functions to do what you want:

where orderdate like '__/__/2010%'

However, you should really put your effort into storing the date using a correct format — YYYY-MM-DD for strings at least.