SQLserver 向表中添加工作日，不排除节假日，只排出星期六星期日

<span style="color: #008080;">--</span><span style="color: #008080;">--添加一年的工作日</span>
<span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@num</span> <span style="color: #0000ff;">int</span> 
<span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@count</span> <span style="color: #0000ff;">int</span> 
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@num</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@count</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> 
<span style="color: #0000ff;">WHILE</span> <span style="color: #008000;">@num</span> <span style="color: #808080;"><=</span><span style="color: #800000; font-weight: bold;">365</span>  <span style="color: #008080;">--</span><span style="color: #008080;">时间跨度有闰年的就是366天</span>
<span style="color: #0000ff;">begin</span> 
<span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">into</span> WeekDays <span style="color: #0000ff;">VALUES</span> (   <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">10</span>),<span style="color: #ff00ff;">dateadd</span>(dd,<span style="color: #008000;">@num</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2017-07-05</span><span style="color: #ff0000;">‘</span>),<span style="color: #800000; font-weight: bold;">23</span>),<span style="color: #800000; font-weight: bold;">1</span>)<span style="color: #008080;">--</span><span style="color: #008080;">  2017-07-05 星期三</span>
<span style="color: #0000ff;">if</span> <span style="color: #008000;">@count</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">2</span>
<span style="color: #0000ff;">begin</span> 
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@num</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@num</span><span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">3</span>
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@count</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@count</span><span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">3</span>
<span style="color: #0000ff;">end</span> 
<span style="color: #0000ff;">else</span> 
<span style="color: #0000ff;">begin</span> 
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@num</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@num</span><span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span>
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@count</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@count</span><span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span> 
<span style="color: #0000ff;">end</span>
<span style="color: #0000ff;">IF</span> <span style="color: #008000;">@count</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">8</span>
<span style="color: #0000ff;">set</span> <span style="color: #008000;">@count</span> <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>
<span style="color: #0000ff;">end</span>

SQLserver 向表中添加工作日，不排除节假日，只排出星期六星期日

标签：