日期处理方法代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】日期处理方法
作者:Dezai.CN / 发布于2011/8/9/ 750
<div><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">)去掉时分秒 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> @ </span><span style="color: rgb(0,0,255)">datetime</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> @ </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">() </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">'2003-7-1 10:00:00' </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> @,</span><span style="color: rgb(255,0,255)">DATEADD</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">,@), </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">) 

</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">）显示星期几 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datename</span><span style="color: rgb(0,0,0)">(weekday,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) 

</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">）如何取得某个月的天数 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">月份 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">2003-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-15</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> ,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">2003-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-15</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 

另外，取得本月天数 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">month</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">GetDate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">month</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">GetDate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-15</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> ,</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">month</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">GetDate</span><span style="color: rgb(0,0,0)">()) </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">month</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">GetDate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-15</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">) 

任意月份的最大天数
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(dd,</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(mm,</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">Dateadd</span><span style="color: rgb(0,0,0)">(mm,</span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(mm,</span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()),</span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">)))) 


或者使用计算本月的最后一天的脚本，然后用DAY函数区最后一天 
</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">Day</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(ms,</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">DATEADD</span><span style="color: rgb(0,0,0)">(mm, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(m,</span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">))) 

</span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)">）判断是否闰年： 

</span><span style="color: rgb(0,0,255)">SELECT</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">case</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(mm, </span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(ms,</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">DATEADD</span><span style="color: rgb(0,0,0)">(yy, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(yy,</span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()), </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)">)))) </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">28</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">平年</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">闰年</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 

或者 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">case</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">datename</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-02-01</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(mm,</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">datename</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-02-01</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)) 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">28</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">平年</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">闰年</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 

</span><span style="color: rgb(128,0,0); font-weight: bold">5</span><span style="color: rgb(0,0,0)">）一个季度多少天 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">tinyint</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">smalldatetime</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">month</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()) 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">between</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">and</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">between</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">and</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">6</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">between</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">7</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">and</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">9</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">7</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">datename</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">year</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">())</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">convert</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">10</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(0,128,0)">@m</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">-01</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">datediff</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">day</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,255)">dateadd</span><span style="color: rgb(0,0,0)">(mm,</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(0,0,0)">)) </span></div>
试试其它关键字
　日期处理　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)