date_format('{TX_DATE}', 'yyyyMM')
date_format('2022-09-02','yyyyMM') ---> 20220902
select
current_date()
,date_add('{TX_DATE}',8 - dayofweek('{TX_DATE}')) as week_last_day -- 本周最后一天_周日
,date_format(date_add('{TX_DATE}',8 - dayofweek('{TX_DATE}')), 'yyyyMMdd')
,date_add('{TX_DATE}',2 - dayofweek('{TX_DATE}')) as week_first_day
看起来上面是错误的 :
正确的 :
select date_add('2022-09-11',1 - case when dayofweek('2022-09-11') = 1 then 7 else dayofweek('2022-09-11') - 1 end) ,date_format(date_add('2022-09-11',7 - case when dayofweek('2022-09-11') = 1 then 7 else dayofweek('2022-09-11') - 1 end), 'yyyyMMdd')
去掉空格语法:
where SUBSTR(trade_time ,1,10) ='2019-04-13'
--and regexp_replace(NVL(settle_key,''),'[\\s]+|[\u3000]+|[,]','')
---in ('03102900000499931007060401181051')
limit 11 ;