数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
T-SQL 生成 两个新的真正的公历年历

作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站

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


declare @ datetime
set @ = '1995-02-25 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




相关文章

相关软件