截取日期字段中的年和月代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】截取日期字段中的年和月
作者:Dezai.CN / 发布于2012/7/31/ 708
截取日期字段中的年和月
<div>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">--</span><span style="color: rgb(0, 128, 128); line-height: 18px;">如果是日期型</span><span style="color: rgb(0, 128, 128); line-height: 18px;">
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">declare</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@a</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 0, 255); line-height: 18px;">datetime</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">set</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@a</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(128, 128, 128); line-height: 18px;">=</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(255, 0, 0); line-height: 18px;">2011-02-15</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">select</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(255, 0, 255); line-height: 18px;">convert</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(0, 0, 255); line-height: 18px;">varchar</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">6</span><span style="color: rgb(0, 0, 0); line-height: 18px;">),</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@a</span><span style="color: rgb(0, 0, 0); line-height: 18px;">,</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">112</span><span style="color: rgb(0, 0, 0); line-height: 18px;">)
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">go</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">--</span><span style="color: rgb(0, 128, 128); line-height: 18px;">如果是字符型</span><span style="color: rgb(0, 128, 128); line-height: 18px;">
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">d</span></span></span><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">eclare</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@a</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 0, 255); line-height: 18px;">varchar</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">10</span><span style="color: rgb(0, 0, 0); line-height: 18px;">)
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">set</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@a</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(128, 128, 128); line-height: 18px;">=</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
<span style="color: rgb(255, 0, 0); line-height: 18px;">'</span></span><span style="color: rgb(255, 0, 0); line-height: 18px;"><span style="color: rgb(255, 0, 0); line-height: 18px;">2011-02-15</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(0, 0, 0); line-height: 18px;"> </span></span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">select</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(128, 128, 128); line-height: 18px;">left</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(255, 0, 255); line-height: 18px;">replace</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@a</span><span style="color: rgb(0, 0, 0); line-height: 18px;">,</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(255, 0, 0); line-height: 18px;">-</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(0, 0, 0); line-height: 18px;">,</span><span style="color: rgb(255, 0, 0); line-height: 18px;">''</span><span style="color: rgb(0, 0, 0); line-height: 18px;">),</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">6</span><span style="color: rgb(0, 0, 0); line-height: 18px;">)<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">--这句意思空格是替换2011-02-15所有&rsquo;-&lsquo;号结果：20110215从左边截取6位得到201102</span></span></span></span></span></span>

<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">--</span><span style="color: rgb(0, 128, 128); line-height: 18px;">日期型</span><span style="color: rgb(0, 128, 128); line-height: 18px;">
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">declare</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@date</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 0, 255); line-height: 18px;">datetime</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">set</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@date</span><span style="color: rgb(128, 128, 128); line-height: 18px;">=</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(255, 0, 0); line-height: 18px;">2011-02-15</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">select</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(255, 0, 255); line-height: 18px;">convert</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(0, 0, 255); line-height: 18px;">char</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">6</span><span style="color: rgb(0, 0, 0); line-height: 18px;">),</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@date</span><span style="color: rgb(0, 0, 0); line-height: 18px;">,</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">112</span><span style="color: rgb(0, 0, 0); line-height: 18px;">)
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">/*</span><span style="color: rgb(0, 128, 128); line-height: 18px;">*
------ 201102---- *</span><span style="color: rgb(0, 128, 128); line-height: 18px;">*/</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span></span></span></span></span></span></span>

<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">--</span><span style="color: rgb(0, 128, 128); line-height: 18px;">字符型</span><span style="color: rgb(0, 128, 128); line-height: 18px;">
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">declare</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@date</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 0, 255); line-height: 18px;">varchar</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">10</span><span style="color: rgb(0, 0, 0); line-height: 18px;">)
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">set</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@date</span><span style="color: rgb(128, 128, 128); line-height: 18px;">=</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(255, 0, 0); line-height: 18px;">2011-02-15</span><span style="color: rgb(255, 0, 0); line-height: 18px;">'</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span></span></span></span></span></span></span></span></span>
<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 0); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;"><span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 0, 255); line-height: 18px;">select</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(255, 0, 255); line-height: 18px;">convert</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(0, 0, 255); line-height: 18px;">char</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">6</span><span style="color: rgb(0, 0, 0); line-height: 18px;">),</span><span style="color: rgb(255, 0, 255); line-height: 18px;">cast</span><span style="color: rgb(0, 0, 0); line-height: 18px;">(</span><span style="color: rgb(0, 128, 0); line-height: 18px;">@date</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 0, 255); line-height: 18px;">as</span><span style="color: rgb(0, 0, 0); line-height: 18px;">
</span><span style="color: rgb(0, 0, 255); line-height: 18px;">datetime</span><span style="color: rgb(0, 0, 0); line-height: 18px;">),</span><span style="color: rgb(128, 0, 0); line-height: 18px; font-weight: bold;">112</span><span style="color: rgb(0, 0, 0); line-height: 18px;">)<span><span style="text-align: left; line-height: 23px; font-family: monospace; font-size: 12px; white-space: pre;"><span style="color: rgb(0, 128, 128); line-height: 18px;">--这句意思强转类型string转换datetime</span></span></span></span></span></span></span></span></span></span></span></span>
</div>
试试其它关键字
　截取日期字段中的年和月　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)