T-SQL 生成 两个新的真正的公历年历

转载：T-SQL 生成 两个新的真正的公历年历 

-- 两个新年历
-- 增加了日期所在月及年的周次!
-- 星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)
-- 注意 datename 的值会因 SQL Server 语言版本或日期格式有所差异!
-- 本测试环境为: SQL Server 2000 简体中文版 + Windows 简体中文版


declare  @  datetime
set  @  =   ' 2008-01-01 11:00:50 '   --  1995-01-01 正好是个星期日


select  @  as  日期
      , dateadd ( year , datediff ( year , 0 ,@), 0 )  as  所在年的第一天
      , dateadd ( year , 1 + datediff ( year , 0 ,@), 0 ) - 1   as  所在年的最后一天 
      , dateadd (quarter, datediff (quarter, 0 ,@), 0 )  as  所在季的第一天 
      , dateadd (quarter, 1 + datediff (quarter, 0 ,@), 0 ) - 1   as  所在季的最后一天
      , dateadd ( month , datediff ( month , 0 ,@), 0 )  as  所在月的第一天 
      , dateadd ( month , 1 + datediff ( month , 0 ,@), 0 ) - 1   as  所在月的最后一天
      , dateadd (week, datediff (week, 0 ,@), 0 )  as  所在周的第一天 
      , dateadd (week, 1 + datediff (week, 0 ,@), 0 ) - 1   as  所在周的最后一天

 

select   month ( min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  as  月份
       , datepart (week, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  as  WeekOfYear
       , datediff (week
       , dateadd ( month , datediff ( month , 0 , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))), 0 )
       , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))
        )  + 1   as  WeekOfMonth
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期日 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              else   null   end   as  星期日             
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期一 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期一 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              else   null   end   as  星期一
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期二 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期二 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1   
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期二 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2  
              else   null   end   as  星期二
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期三 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期三 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1   
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期三 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期三 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3  
              else   null   end   as  星期三
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期四 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1   
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4  
              else   null   end   as  星期四
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期五 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1   
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 5 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 5 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 5  
              else   null   end   as  星期五
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期六 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1   
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 5 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 5 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 5  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 6 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 6 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 6  
              else   null   end   as  星期六
from
(
select   0   as  i
union   all
select   1
union   all
select   2
union   all
select   3
union   all
select   4
union   all
select   5
union   all
select   6
union   all
select   7
union   all
select   8
union   all
select   9
union   all
select   10
union   all
select   11
) M
,
(
select   0   as  i
union   all
select   1
union   all
select   2
union   all
select   3
union   all
select   4
union   all
select   5
union   all
select   6
union   all
select   7
union   all
select   8
union   all
select   9
union   all
select   10
union   all
select   11
union   all
select   12
union   all
select   13
union   all
select   14
union   all
select   15
union   all
select   16
union   all
select   17
union   all
select   18
union   all
select   19
union   all
select   20
union   all
select   21
union   all
select   22
union   all
select   23
union   all
select   24
union   all
select   25
union   all
select   26
union   all
select   27
union   all
select   28
union   all
select   29
union   all
select   30
) d
where   datediff ( month , dateadd ( year , datediff ( year , 0 ,@), 0 ), dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))  =  m.i
         
group   by   datediff ( month , 0 , dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))
   , datepart (week, dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))


-- ==============================================

 

select   month ( min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  as  月份
--        ,datepart(week,min(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) as WeekOfYear
       , datediff (week
       , case   when   datename (weekday, dateadd ( day , 0 , datediff ( day , 0 , dateadd ( year , datediff ( year , 0 ,@), 0 ))))  =   ' 星期日 '
                   then   dateadd ( day , - 1 , dateadd ( day , 0 , datediff ( day , 0 , dateadd ( year , datediff ( year , 0 ,@), 0 ))))
              else   dateadd ( day , 0 , datediff ( day , 0 , dateadd ( year , datediff ( year , 0 ,@), 0 )))
         end      
       , case   when   datename (weekday, dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  =   ' 星期日 '
                   then   dateadd ( day , - 1 , dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))
              else   dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))
         end      
        )  + 1   as  MyWeekOfYear
       , datediff (week
       , case   when   datename (weekday, dateadd ( month , datediff ( month , 0 , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))), 0 )) =   ' 星期日 '
                   then   dateadd ( month , datediff ( month , 0 , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))), 0 )  - 1
              else   dateadd ( month , datediff ( month , 0 , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))), 0 )
         end  
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期日 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) - 1
              else   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))
         end      
      )         + 1   as  MyWeekOfMonth
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期一 '
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) 
              else   null   end   as  星期一 
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期二 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期二 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              else   null   end   as  星期二
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期三 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期三 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期三 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2  
              else   null   end   as  星期三
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期四 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期四 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3  
              else   null   end   as  星期四
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期五 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4 )  =   ' 星期五 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4  
              else   null   end   as  星期五
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期六 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 5 )  =   ' 星期六 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 5 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 5  
              else   null   end   as  星期六
       , case   when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))))  =   ' 星期日 '  
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 1 )  =   ' 星期日 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 1  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 2 )  =   ' 星期日 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 2  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 3 )  =   ' 星期日 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 3  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 4 )  =   ' 星期日 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 4  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 5 )  =   ' 星期日 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 5 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 6  
              when   datename (weekday, min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))) + 6 )  =   ' 星期日 '
                   and   datediff ( month , min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))), min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 5 ) = 0
                   then   min ( dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  + 6  
              else   null   end   as  星期日      

 

from
(
select   0   as  i
union   all
select   1
union   all
select   2
union   all
select   3
union   all
select   4
union   all
select   5
union   all
select   6
union   all
select   7
union   all
select   8
union   all
select   9
union   all
select   10
union   all
select   11
) M
,
(
select   0   as  i
union   all
select   1
union   all
select   2
union   all
select   3
union   all
select   4
union   all
select   5
union   all
select   6
union   all
select   7
union   all
select   8
union   all
select   9
union   all
select   10
union   all
select   11
union   all
select   12
union   all
select   13
union   all
select   14
union   all
select   15
union   all
select   16
union   all
select   17
union   all
select   18
union   all
select   19
union   all
select   20
union   all
select   21
union   all
select   22
union   all
select   23
union   all
select   24
union   all
select   25
union   all
select   26
union   all
select   27
union   all
select   28
union   all
select   29
union   all
select   30
) d
where   datediff ( month , dateadd ( year , datediff ( year , 0 ,@), 0 ), dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))  =  m.i
         
group   by   datediff ( month , 0 , dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))
        , datediff (week
        , case   when   datename (weekday, dateadd ( day , 0 , datediff ( day , 0 , dateadd ( year , datediff ( year , 0 ,@), 0 ))))  =   ' 星期日 '
                    then   dateadd ( day , - 1 , dateadd ( day , 0 , datediff ( day , 0 , dateadd ( year , datediff ( year , 0 ,@), 0 ))))
               else   dateadd ( day , 0 , datediff ( day , 0 , dateadd ( year , datediff ( year , 0 ,@), 0 )))
          end      
        , case   when   datename (weekday, dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))  =   ' 星期日 '
                    then   dateadd ( day , - 1 , dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 ))))))
               else   dateadd ( day , 0 , datediff ( day , 0 , dateadd ( day ,d.i, dateadd ( month ,m.i, dateadd ( year , datediff ( year , 0 ,@), 0 )))))
          end )  + 1