Hive comes with some handy functions for transforming dates. These can be helpful when working with date dimension tables and performing time-based comparisons and aggregations.
e.g. Convert a native Hive date formatted date string:
date_format(myDate,'dd-MM-yyyy')
Return the week number (within the year) of a particular date – i.e. first week of the year is 1, the week of new year’s eve is 52, etc:
weekofyear(myDate)
Other less obvious examples
Current month’s name (e.g. January, February, etc):
date_format(myDate, 'MMMMM')
First date of the current quarter:
cast(trunc(add_months(myDate,-pmod(month(myDate)-1,3)),'MM') as date)
Last date of the current quarter:
cast(date_add(trunc(add_months(myDate,3-pmod(month(myDate)-1,3)),'MM'),-1) as date)
Day number of the current quarter (e.g. April 2nd is day 2 of the second quarter, December 9th is day 70 of the fourth quarter, etc):
datediff(myDate,cast(trunc(add_months(myDate,-pmod(month(myDate)-1,3)),'MM') as date))+1
Analytics Anvil 