Calendar week# in the month(Hive Query)

 This is for someone who wants to get the week# of specific date. When I searched this on google, there was 1 way to calcualte the day# as following variable "calcul_weekno". But what I'm trying to is the week# on calendar. Thus I've searched the other way and made the below variables "calendar_weekno"

 Anyway, below code would help you who find this blog.

-- Declare the date

SET dt = '2020-10-28';

-- See the date
SELECT ${hiveconf:dt};

-- Check the weekofyear() function
SELECT weekofyear(${hiveconf:dt});

-- Check the day() function
SELECT day(${hiveconf:dt});

SELECT weekofyear(${hiveconf:dt}) - weekofyear(last_day(add_months(${hiveconf:dt}, -1))) AS week_no;

SELECT ${hiveconf:dt} AS stnd_date,
                    last_day(add_months(${hiveconf:dt}, -1)) AS lmth_end,
                    date_add(last_day(add_months(${hiveconf:dt}, -1)), 1) AS thmth_begin,
                    weekofyear(${hiveconf:dt}) AS day_weekno,
                    weekofyear(last_day(add_months(${hiveconf:dt}, -1))) AS lmth_end_weekno,
                    weekofyear(date_add(last_day(add_months(${hiveconf:dt}, -1)), 1)) AS thmth_begin_weekno, -- Calculating week #
 CASE
     WHEN day(${hiveconf:dt})%7 = 0 THEN day(${hiveconf:dt})/7
     ELSE floor(day(${hiveconf:dt})/7)+1
 END AS calcul_weekno, -- Calendar base week #
 CASE
     WHEN weekofyear(last_day(add_months(${hiveconf:dt}, -1))) = weekofyear(last_day(add_months(${hiveconf:dt}, -1))) THEN weekofyear(${hiveconf:dt}) - weekofyear(last_day(add_months(${hiveconf:dt}, -1))) + 1
     ELSE weekofyear(${hiveconf:dt}) - weekofyear(last_day(add_months(${hiveconf:dt}, -1)))
 END AS calendar_weekno;