1

Morning, Title says it all. I cannot for the life of me figure out how to obtain the first weekday of the current month (or previous month etc.) in HQL.

So if today's date were to be evaluated, it should return 2/3/2020 as the date, since the 3rd was the first weekday of this month.

I've tried case statements to evaluate the first day of the month, and if it is a saturday, add 2 days, a sunday, add 1 but it is not working and I receive the following error: ERROR: Prepare error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:452 extraneous input ',' expecting KW_THEN near ''line 1:626 extraneous input ',' expecting KW_THEN near ''

case when date_format(date_add(current_date, 1 - day(current_date)),'u')=6, then  to_date(mydate) = date_add(date_add(current_date, 1 - day(current_date)+2))
        when date_format(date_add(current_date, 1 - day(current_date)),'u')=7, then  to_date(mydate) = date_add(date_add(current_date, 1 - day(current_date)+1))
            else to_date(mydate) = date_add(date_add(current_date, 1 - day(current_date))) end

Please help!

@Vamsi Prabhala

I tried this however I receive a result of 1. I need a date returned, specifically 2/3/2020 for this month.

case when date_format(date_add(current_date, 1 - day(current_date)),'u')> 5 then  
to_date(mydate)= to_date(date_add(date_add(current_date, 1 - day(current_date)),8-cast(date_format(date_add(current_date, 1 - day(current_date)),'u') as int)%8))
    else to_date(mydate) = date_add(current_date, 1 - day(current_date)) end
Mark
  • 327
  • 1
  • 7
  • 14
  • Does this previous post help? https://stackoverflow.com/questions/22982904/hive-date-function-to-achieve-day-of-week – Jan Held Feb 06 '20 at 15:00
  • I've been able to determine what day of week it is with the following; when date_format(current_date,'u')=1 (1= monday, 2 = Tuesday etc.) However I can't figure out how to incorporate that to determine if that is the first weekday of the month. – Mark Feb 06 '20 at 15:03
  • So if today's date were to be evaluated, it should return 2/3/2020 as the date, since the 3rd was the first weekday of this month. Does that make sense? – Mark Feb 06 '20 at 15:05
  • Ah now I understand. Sorry, my fault. Unfortunately I have no real idea, how to determine that in Hive. – Jan Held Feb 06 '20 at 15:47

1 Answers1

1

One option is to get the first day in month and then add days if the first day in month is a weekend. By default weekday 1 = Monday, 2 = Tuesday ... 6 = Saturday, 7 = Sunday. 

select dt
      ,case when date_format(first_day_in_month,'u') > 5 
            then date_add(first_day_in_month,8-cast(date_format(first_day_in_month,'u') as int))
       else first_day_in_month end as first_week_day
from (select dt
            ,date_sub(dt,cast(date_format(dt,'d') as int)-1) as first_day_in_month
      from tbl 
     ) t 
;

Note that the date_format function works in Hive versions 1.2.0 and above.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58