Hive SQL 计算指定日期本周的第一天和最后一天

selectday,dayofweek(day)as dw1,date_add(day,1 - dayofweek(day))as Su_s -- 周日_start,date_add(day,7 - dayofweek(day))

香山上的麻雀1008

3288人浏览 · 2021-05-20 17:59:25

香山上的麻雀1008 · 2021-05-20 17:59:25 发布

select
     day
    ,dayofweek(day)                                                                    as dw1
    ,date_add(day,1 - dayofweek(day))                                                  as Su_s -- 周日_start
    ,date_add(day,7 - dayofweek(day))                                                  as Sa_e -- 周六_end
    ,case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end                   as dw2
    ,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as Mo_s -- 周一_start
    ,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as Su_e -- 周日_end
from (
    select '2021-05-20' as day
) t1