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

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


declare @ datetimeset @ = '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)))</pre><p>上一个：<a href="/show/d8400fb610d59e4f.htm">通过sql语句获取数据库的基本信息</a><br/>下一个：<a href="/show/a4e5311332f23b36.htm">空间数据挖掘技术理论及方法</a></p><div class="piclist">更多图片编程知识:<ul><li><a href="/show/a37d86128b7662ce.htm"><img src='/upload/20121015/20090901004257119armglpomdza.jpg' width=105 height=105><br/>sqlserver2005 xml字段的读写操作</a></li><li><a href="/show/9ed869465c836a81.htm"><img src='/upload/2013111603/20120908085839374.png' width=105 height=105><br/>Vaadin Web应用开发教程(46): 开始使用SQLContainer</a></li><li><a href="/show/8d2c9326d3be17e9.htm"><img src='/upload/2013111617/20110315142939892.png' width=105 height=105><br/>SQL Server 2008中的代码安全（四）：主密钥</a></li><li><a href="/show/dfbb7e58f195f70b.htm"><img src='/upload/2013111619/20110525161317945.gif' width=105 height=105><br/>SQL点滴3—一个简单的字符串分割函数</a></li><li><a href="/show/776466698e4ff032.htm"><img src='/upload/2013111619/20120309050142738.gif' width=105 height=105><br/>解决Sybase客户端执行SQL遇到中文乱码问题</a></li><li><a href="/show/645de8c604aff0b6.htm"><img src='/upload/2013111619/20130701093526646.jpg' width=105 height=105><br/>scott用户sql操作</a></li></ul></div></div>
  <div class="zykmore"><script type="text/javascript" src="/js/s4.js"></script></div><div class="more"><dl><dt>更多<a href="/list/17_1.htm">SQLServer</a>疑问解答：</dt><dd><a target="_blank" href="/show/cddfccc1916d8a6e.htm">配置MSSQL复制指定快照文件夹提示：不是有效的路径或文件名</a></dd><dd><a target="_blank" href="/show/06dd9b2b9f09810f.htm">详细解读varchar和Nvarchar区别</a></dd><dd><a target="_blank" href="/show/aa1d1257d4d974f5.htm">SQL SERVER 2005 同步复制技术</a></dd><dd><a target="_blank" href="/show/a31ee5a6b483a72d.htm">进程未能大容量复制到表 解决方法</a></dd><dd><a target="_blank" href="/show/c096f26db222cb32.htm">MSSql实例教程:MSSql数据库同步</a></dd><dd><a target="_blank" href="/show/1b344474e99cab59.htm">SQLServer2000同步复制技术实现（分发和订阅）</a></dd><dd><a target="_blank" href="/show/131c294eb21bd79f.htm">sqlserver</a></dd><dd><a target="_blank" href="/show/4960400c7f537198.htm">SQLSERVER</a></dd><dd><a target="_blank" href="/show/882bbb04ab3b2c1f.htm">测试 sqlserver 最大用户数连接</a></dd><dd><a target="_blank" href="/show/121218045cb7032e.htm">写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的</a></dd><dd><a target="_blank" href="/show/32ab4f806431a48f.htm">sqlserver2008的安装问题。</a></dd><dd><a target="_blank" href="/show/138c6021b9e4a45e.htm">为什么安装的SQL server 2008我的没有MSSQLSERVER协议啊？</a></dd><dd><a target="_blank" href="/show/d9e60316fa715602.htm">怎样把exel表里的数据复制到sqlserver表里</a></dd><dd><a target="_blank" href="/show/e2b1a07ccc72645a.htm">sqlserver数据库主键和外键问题</a></dd><dd><a target="_blank" href="/show/761bfb7fd837d775.htm">sqlserver 中的左表连接查询和右表连接查询有啥不同？有什么用？</a></dd></dl></div>
</div>
<div id="zykleft">
  <div class="leftm">
    <div id="logo"><a href="http://www.zzzyk.com/">站长资源库</a>
    	<div class="clear"></div><script type="text/javascript" src="/js/i7f.js"></script></div>
      <script type="text/javascript" src="/js/sharen.js"></script><div class="clear"></div>
    <dl class="list"><dd><a href="/list/14_1.htm">Oracle</a></dd><dd><a href="/list/15_1.htm">MySQL</a></dd><dd><a href="/list/16_1.htm">Access</a></dd><dd><a href="/list/17_1.htm">SQLServer</a></dd><dd><a href="/list/18_1.htm">DB2</a></dd><dd><a href="/list/19_1.htm">Excel</a></dd><dd><a href="/list/22_1.htm">SQLite</a></dd><dd><a href="/list/63_1.htm">SYBASE</a></dd><dd><a href="/list/68_1.htm">Postgres</a></dd><dd><a href="/list/73_1.htm">mongodb</a></dd></dl><dl class="notice">如果你遇到<a href="/database.htm">数据库</a>难题：<br/>访问www.zzzyk.com 试试</dl>
  </div>
  <div class="leftm"><dl><dt><script type="text/javascript" src="/js/s5.js"></script></dt></dl></div>
  <div class="leftm"><dl><dt><script type="text/javascript" src="/js/s6.js"></script></dt></dl></div>
</div></div>
<div class="clear"></div>
<div class="bdshare">
  <script type="text/javascript" src="/js/share.js"></script>
</div>
<div class="clear"></div>
<div class="foot">CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved<br/>部分文章来自网络，<script type="text/javascript" src="/images/my.js"></script>
</div>
</body>
</html>