CODEWITHSUNDEEP
sqlhive
user1023627
user1023627

Reputation: 183

How to retrieve the WeekofMonth for a given date in Hive

I have a date field in Hive 2018-06-10, from which i need to get WeekOfMonth

WEEKOFYEAR(order_time)

I need output for 2018-06-10 as 3 (which is 3rd week. assuming week starts from Sunday)

Is there any built in function in Hive to retrieve WeekofMonth. I couldn't find any. I tried below to convert based on minutes and seconds but 

from_unixtime(unix_timestamp(CURRENT_DATE())+7200)

But the above is not giving correct value

Upvotes: 1

Views: 535

Answers (2)

leftjoin
leftjoin

Reputation: 38335

Also you can use date_format function:

select date_format('2018-06-10','W');

See more format patterns here: SimpleDateFormat

Upvotes: 0

nobody
nobody

Reputation: 11090

For the week of the month, you can get the day part of the month and divide by 7.

select case 
  when DAYOFMONTH(order_time)%7 = 0  
       then DAYOFMONTH(order_time)/7 
       else DAYOFMONTH(order_time)/7 + 1 
  end

Upvotes: 1

Related Questions