Search code examples
sqlhadoophivenosqlcloudera

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?

Solution

  • 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.