判断指定日期是星期几

 

 1 -- 判断指定日期是星期几
 2 select 
 3      etl_dt
 4     ,case when dayofweek(etl_dt) = 1 then 7 else dayofweek(etl_dt) - 1 end as week1 -- 周一至周七
 5     ,datediff(etl_dt,'1970-01-05') % 7 + 1                                 as week2 -- 周一至周七
 6     ,case when dayofweek(etl_dt) = 1 then 0 else dayofweek(etl_dt) - 1 end as week3 -- 周日为0
 7     ,datediff(etl_dt,'1970-01-04') % 7                                     as week4 -- 周日为0
 8     ,dayofweek(etl_dt)                                                     as week5 -- 周日为1
 9     ,datediff(etl_dt,'1970-01-04') % 7 + 1                                 as week6 -- 周日为1
10 from (
11     select '2018-04-16' as etl_dt union all
12     select '2018-04-17' as etl_dt union all
13     select '2018-04-18' as etl_dt union all
14     select '2018-04-19' as etl_dt union all
15     select '2018-04-20' as etl_dt union all
16     select '2018-04-21' as etl_dt union all
17     select '2018-04-22' as etl_dt
18 ) t1
19 order by etl_dt
20 ;

 

posted @ 2018-04-16 23:31  chenzechao  阅读(777)  评论(0)    收藏  举报