T-SQL生成一个简易的公历年历T-SQL含日期所在月及年的周次

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

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


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


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


selectdateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))as[Date]
,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))as[WeekDayName]
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))as[WeekDay]
,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))asWeekOfYear
,datediff
(
week
,casewhendatename(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))='星期日'
          thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))
     elsedateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))
end    

,casewhendatename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))='星期日'
          thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
     elsedateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end    
)
+1asMyWeekOfYear


,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))+1asWeekOfMonth
,datediff
(week
,casewhendatename(weekday,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))='星期日' 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>