sql的上周六到本周五的方法

sql的上周六到本周四的方法

     select * 
        , case when  ds >= to_char(DATEADD(dt1,5-WEEKDAY(dt1),'dd'),'yyyymmdd')
                        and ds <=to_char(DATEADD(dt1,6-WEEKDAY(dt1),'dd'),'yyyymmdd')
                        then to_char(DATEADD(dt1,5-WEEKDAY(dt1),'dd'),'yyyymmdd') 
                    when ds >= to_char(DATEADD(dt1,-WEEKDAY(dt1)-2,'dd'),'yyyymmdd')
                    and ds <= to_char(DATEADD(dt1,4-WEEKDAY(dt1),'dd'),'yyyymmdd')
                    then to_char(DATEADD(dt1,-WEEKDAY(dt1)-2,'dd'),'yyyymmdd')
                    end as start_ds 

        , case when  ds >= to_char(DATEADD(dt1,5-WEEKDAY(dt1),'dd'),'yyyymmdd')
                        and ds <= to_char(DATEADD(dt1,11-WEEKDAY(dt1),'dd'),'yyyymmdd')
                        then to_char(DATEADD(dt1,11-WEEKDAY(dt1),'dd'),'yyyymmdd')
                    when ds >= to_char(DATEADD(dt1,-WEEKDAY(dt1)-2,'dd'),'yyyymmdd')
                    and ds <= to_char(DATEADD(dt1,4-WEEKDAY(dt1),'dd'),'yyyymmdd')
                    then to_char(DATEADD(dt1,4-WEEKDAY(dt1),'dd'),'yyyymmdd')
                    end as end_ds    
        from tmp_da_income_support_quickbi 

第二种办法：

通过datediff 在 0-6判定；

with tb1 as(
    select distinct date_time as login_date
    from dim_calendar
    where date_id between to_char(DATEADD(getdate(),-30,'dd'),'yyyymmdd') and to_char(DATEADD(getdate(),-2,'dd'),'yyyymmdd')
    and WEEKDAY(date_time) = 5),

tb2 as(
    select distinct date_time as login_date
    from dim_calendar
    where date_id between to_char(DATEADD(getdate(),-30,'dd'),'yyyymmdd') and to_char(DATEADD(getdate(),-2,'dd'),'yyyymmdd'))

select distinct concat(to_char(tb1.login_date,'yyyymmdd'),'-',to_char(dateadd(tb1.login_date,6,'dd'),'yyyymmdd')) week,
tb1.login_date first_date
,tb2.login_date login_date
from tb2 
left join tb1 on datediff(tb2.login_date,tb1.login_date,'dd') between 0 and 6
;