日期转换参数代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】日期转换参数
作者:Dezai.CN / 发布于2011/8/9/ 870
<div><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </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(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">(), </span><span style="color: rgb(128,0,0); font-weight: bold">120</span><span style="color: rgb(0,0,0)"> )
</span><span style="color: rgb(128,0,0); font-weight: bold">2004</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">09</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">12</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">11</span><span style="color: rgb(0,0,0)">:</span><span style="color: rgb(128,0,0); font-weight: bold">06</span><span style="color: rgb(0,0,0)">:</span><span style="color: rgb(128,0,0); font-weight: bold">08</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)">replace</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">replace</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,255)">replace</span><span style="color: rgb(0,0,0)">(</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(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">(), </span><span style="color: rgb(128,0,0); font-weight: bold">120</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(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)"> </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(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(255,0,0)">''</span><span style="color: rgb(0,0,0)">)
</span><span style="color: rgb(128,0,0); font-weight: bold">20040912110608</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)">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">12</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">111</span><span style="color: rgb(0,0,0)"> )
</span><span style="color: rgb(128,0,0); font-weight: bold">2004</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">09</span><span style="color: rgb(128,128,128)">/</span><span style="color: rgb(128,0,0); font-weight: bold">12</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)">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">12</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">112</span><span style="color: rgb(0,0,0)"> )
</span><span style="color: rgb(128,0,0); font-weight: bold">20040912</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)">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">12</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">102</span><span style="color: rgb(0,0,0)"> )
</span><span style="color: rgb(128,0,0); font-weight: bold">2004.09</span><span style="color: rgb(0,0,0)">.</span><span style="color: rgb(128,0,0); font-weight: bold">12</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)">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)">) </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"> 2009-06-01 00:00:00.000</span><span style="color: rgb(0,128,128)">
</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(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> product </span><span style="color: rgb(0,0,255)">where</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)">,modiDate,</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,128,128)">--</span><span style="color: rgb(0,128,128)">如何查询本日、本月、本年的记录SQL</span><span style="color: rgb(0,128,128)">
</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(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> loanInfo </span><span style="color: rgb(0,0,255)">where</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)">(date)</span><span style="color: rgb(128,128,128)">=</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(0,0,255)">select</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(0,0,255)">from</span><span style="color: rgb(0,0,0)"> loanInfo </span><span style="color: rgb(0,0,255)">where</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)">(date)</span><span style="color: rgb(128,128,128)">=</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)">And</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)">(date)</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(128,128,128)">*</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> loanInfo </span><span style="color: rgb(0,0,255)">where</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)">(date)</span><span style="color: rgb(128,128,128)">=</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)">And</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)">(date)</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(128,128,128)">and</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)">(date)</span><span style="color: rgb(128,128,128)">=</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)">getDate</span><span style="color: rgb(0,0,0)">()) 

</span><span style="font-family: "Courier New"; font-size: 13px" class="Apple-style-span"><span style="font-family: "Courier New"; color: rgb(0,0,0)">昨天的记录：
</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">datediff</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">(</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">day</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">[</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">Datetime</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">]</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">getdate</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">())</span><span style="font-family: "Courier New"; color: rgb(128,128,128)">=</span><span style="font-family: "Courier New"; color: rgb(128,0,0); font-weight: bold">1</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> 把Datetime换为你的相应字段，</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">getdate</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">()</span><span style="font-family: "Courier New"; color: rgb(128,128,128)">-</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">Datetime即为时间差。
本月记录：
</span><span style="font-family: "Courier New"; color: rgb(0,0,255)">SELECT</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(128,128,128)">*</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(0,0,255)">FROM</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> 表 </span><span style="font-family: "Courier New"; color: rgb(0,0,255)">WHERE</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(255,0,255)">datediff</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">(</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">month</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">[</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">dateadd</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">]</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">getdate</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">())</span><span style="font-family: "Courier New"; color: rgb(128,128,128)">=</span><span style="font-family: "Courier New"; color: rgb(128,0,0); font-weight: bold">0</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">
本周记录：
</span><span style="font-family: "Courier New"; color: rgb(0,0,255)">SELECT</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(128,128,128)">*</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(0,0,255)">FROM</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> 表 </span><span style="font-family: "Courier New"; color: rgb(0,0,255)">WHERE</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(255,0,255)">datediff</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">(week,</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">[</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">dateadd</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">]</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">getdate</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">())</span><span style="font-family: "Courier New"; color: rgb(128,128,128)">=</span><span style="font-family: "Courier New"; color: rgb(128,0,0); font-weight: bold">0</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">
本日记录：
</span><span style="font-family: "Courier New"; color: rgb(0,0,255)">SELECT</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(128,128,128)">*</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(0,0,255)">FROM</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> 表 </span><span style="font-family: "Courier New"; color: rgb(0,0,255)">WHERE</span><span style="font-family: "Courier New"; color: rgb(0,0,0)"> </span><span style="font-family: "Courier New"; color: rgb(255,0,255)">datediff</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">(</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">day</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">[</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">dateadd</span><span style="font-family: "Courier New"; color: rgb(255,0,0)">]</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">,</span><span style="font-family: "Courier New"; color: rgb(255,0,255)">getdate</span><span style="font-family: "Courier New"; color: rgb(0,0,0)">())</span><span style="font-family: "Courier New"; color: rgb(128,128,128)">=</span><span style="font-family: "Courier New"; color: rgb(128,0,0); font-weight: bold">0</span></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)">DATEADD</span><span style="color: rgb(0,0,0)">(wk, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(wk,</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)">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)">(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)">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)">(qq, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(qq,</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)">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)">(dd, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(dd,</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)">) 

上个月的最后一天 

这是一个计算上个月最后一天的例子。它通过从一个月的最后一天这个例子上减去毫秒来获得。有一点要记住，在Sql Server中时间是精确到毫秒。这就是为什么我需要减去毫秒来获得我要的日期和时间。 

</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)">(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)">(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)">)) 

计算出来的日期的时间部分包含了一个Sql Server可以记录的一天的最后时刻(&ldquo;:</span><span style="color: rgb(128,0,0); font-weight: bold">59</span><span style="color: rgb(0,0,0)">:</span><span style="color: rgb(128,0,0); font-weight: bold">59</span><span style="color: rgb(0,0,0)">:</span><span style="color: rgb(128,0,0); font-weight: bold">997</span><span style="color: rgb(0,0,0)">&rdquo;)的时间。 

去年的最后一天 

连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去毫秒。 

</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)">(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)">)) 

本月的最后一天 

现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。修改需要给用DATEDIFF比较当前日期和&ldquo;</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">01</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">01</span><span style="color: rgb(0,0,0)">&rdquo;返回的时间间隔上加。通过加个月，我计算出下个月的第一天，然后减去毫秒，这样就计算出了这个月的最后一天。这是计算本月最后一天的SQL脚本。 

</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)">(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(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)">(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,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(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)">(wk, </span><span style="color: rgb(255,0,255)">DATEDIFF</span><span style="color: rgb(0,0,0)">(wk,</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)">dateadd</span><span style="color: rgb(0,0,0)">(dd,</span><span style="color: rgb(128,0,0); font-weight: bold">6</span><span style="color: rgb(128,128,128)">-</span><span style="color: rgb(255,0,255)">datepart</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)">getdate</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)">) 

在这个例子里，我使用了&ldquo;本周的星期一&rdquo;的脚本，并作了一点点修改。修改的部分是把原来脚本中&ldquo;</span><span style="color: rgb(255,0,255)">getdate</span><span style="color: rgb(0,0,0)">()&rdquo;部分替换成计算本月的第天，在计算中用本月的第天来替换当前日期使得计算可以获得这个月的第一个星期一。 
</span></div>
试试其它关键字
　日期转换　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)