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

最新推荐文章于 2024-09-26 22:00:00 发布
最新推荐文章于 2024-09-26 22:00:00 发布
阅读量616 收藏
点赞数
文章标签： null sqlserver windows 语言 测试

转载：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

bqb
关注
T-SQL生成两个新的真正的公历年历
zgqtxwd的专栏
04-27 265
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
教你用SQL生成一张带「农历」的日期维度表
SQL数据库开发
02-19 2067
点上方关注“SQL数据库开发”，设为“置顶或星标”，第一时间送达干货之前已经发布过使用存储过程，生存指定年份的日历表《SQL如何制作一张日历维度表》，然后我在文末留了一个思考题：如何将日期...
T-SQL 2 Tips: 1.计算任意两日期之间的"周一"到"周日"分别各有几个! 2.根据出生日期计算精确年龄!
庖丁坊
08-20 4448
http://blog.csdn.net/playyuer/archive/2004/08/20/79699.aspx这两个小技巧,不写不知道,一写吓一跳!都是看似简单,实际做起来就懵,得仔细想一想,才能写对!凡是有日期运算的程序都要细心哦!先说第二个:2.根据出生日期精确计算年龄!  所谓计算精确年龄就是: 生日差一天也不能长一岁!  大家常用,间隔年数算作年龄! 如果需求要精确
T-SQL: 三个通用的与日期相关的,辅助按周(星期日是周的最后一天)汇总的自定义函数
playyuer㊣www.Microshaoft.com I Love UltraEditPlus
11-04 2288
/*每个函数都只有一句话!其实都是从我的另一篇 blog 里抠出来的:T-SQL 生成 两个新的真正的公历年历 http://blog.csdn.net/playyuer/archive/2004/04/07/2860.aspxT-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次http://blog.csdn.net/playyuer/archive/2004/04/
T-SQL:三个通用的与日期相关的,辅助按周(星期日是周的最后一天)汇总的自定义函数
zgqtxwd的专栏
04-27 270
<!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--><script type="text/javascript"
T-SQL 2 Tips: 1.计算任意两日期之间的"周一"到"周日"分别各有几个! 2.根据出生日期计算精确年龄!...
weixin_34087301的博客
08-20 86
这两个小技巧,不写不知道,一写吓一跳!都是看似简单,实际做起来就懵,得仔细想一想,才能写对!凡是有日期运算的程序都要细心哦! 先说第二个: 2.根据出生日期精确计算年龄! 所谓计算精确年龄就是: 生日差一天也不能长一岁! 大家常用,间隔年数算作年龄! 如果需求要精确,如: 保险 之类的,就粗了! 当然还可引申为根据入职日期计算精确的司龄,算加薪之类的需求! 我起初认为很简单,当年也写了...
sql计算任意两日期之间的"周一"到"周日"分别各有几个
sagamaw
09-04 1313
[转]http://www.csip.com.cn/63/n-26663.html这两个小技巧,不写不知道,一写吓一跳!都是看似简单,实际做起来就懵,得仔细想一想,才能写对!凡是有日期运算的程序都要细心哦!先说第二个:2.根据出生日期精确计算年龄!所谓计算精确年龄就是: 生日差一天也不能长一岁!大家常用,间隔年数算作年龄! 如果需求要精确,如: 保险 之类的,就粗了!
T-SQL: 三个通用的与日期相关的,辅助按周(星期日是周的最后一天)汇总的自定义函数...
weixin_34007886的博客
11-04 51
/*每个函数都只有一句话!其实都是从我的另一篇 blog 里抠出来的: T-SQL 生成 两个新的真正的公历年历 http://blog.csdn.net/playyuer/archive/2004/04/07/2860.aspx T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次http://blog.csdn.net/playyuer/archive/2004/04/...
1350多个精品易语言模块
07-26
1350多个精品易语言模块提供下载介绍 1亦思验证码识别1.5免费版.ec 24位转单色位图模块.ec 32张发牌.ec 3D引擎支持库-eOgre.ec 69msn.ec ACCESS 到高级表格.ec Access操作.ec Access数据库压缩修复新建.ec ACCSEE...
1345个易语言模块
01-27
1345个易语言模块，易语言模块大集合，够你用的啦 1亦思验证码识别1.5免费版.ec 24位转单色位图模块.ec 32张发牌.ec 3D引擎支持库-eOgre.ec 69msn.ec ACCESS 到高级表格.ec Access操作.ec Access数据库压缩修复新建....
Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3
06-12
实例027 实现两个变量的互换（不借助 第3个变量） 37 2.3 条件语句 38 实例028 判断某一年是否为闰年 38 实例029 验证登录信息的合法性 39 实例030 为新员工分配部门 40 实例031 用Switch语句根据消费金额计算折扣 ...
根据万年历算八字
五石之瓠 以为大樽 浮于江湖 悠笑人生
07-29 659
生辰八字，每逢结婚生子的时候，总会从长辈的口中念叨出来。那到底什么是生辰八字呢？拿到只有算命先生知道？是一门玄学？ 其实不是，在我国，有农历和阳历两种纪年方法。而阳历属于舶来品，也是当下采用的历法。 但是在古代，我们采用的是农历。农历是一种阴阳历。古人认为太阳属阳, 月亮属阴。结合太阳和月亮的公转周期，加以“闰月"调和而形成的。调和的目的是因为: 地球围绕太阳转一圈(一年)的时间，并不严格等于月亮...
Java Stream流编程入门
2301_77207909的博客
09-21 483
Java StreamAPI的使用与常用方法
guava里常用功能
09-26 201
guava 是 Google 提供的一个 Java 库，提供了很多实用的工具类和方法，可以帮助开发者更高效地编写代码。
excel导出图片---HSSFWorkbook--SXSSFWorkbook
ChickenBro_的博客
09-25 625
平时在工作中，excel导出图片经常会用到，但奈何HSSFWorkbook导出数据数量有限制问题，所以企业里大多都用SXSSFWorkbook格式，很少用HSSFWorkbook。所以今天以这两种格式分别记录下，图片的导出过程。
每日OJ题_牛客_NC40链表相加（二）_链表+高精度加法_C++_Java
最新发布
参考书籍：《C语言程序设计》《数据结构》《C++ Primer》《Effective C++》《STL源码剖析》《现代操作系统》《UNIX环境高级编程》《图解TCP/IP》《图解HTTP》《Linux高性能服务器编程》《剑指Offer》《算法导论》
09-26 273
每日OJ题_牛客_NC40链表相加（二）_链表+高精度加法_C++_Java（模拟⾼精度加法的过程，只不过是在链表中模拟。）
T-SQL详解：从基础到高级
- Unicode字符串常量使用两个字节存储每个字符，而ASCII字符串只用一个字节。 2. **整型常量** - 包括二进制、十六进制和十进制三种形式。十六进制常量以0x开头，如0xEBF；二进制常量由0或1组成，不加引号；十...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值