SQL Server 2008中的创建格式化日期(1)

让我们来创建dbo.format_date 函数，如下所示。 /******Object:UserDefinedFunction[dbo].[format_date] Script Date :05/12/200923:19:35******/ IFEXISTS( SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N '[dbo].[format_date]' ) AND type in (N

让我们来创建dbo.format_date 函数，如下所示。

<ol class="dp-sql">
<li class="alt"><span><span>/****** Object: UserDefinedFunction [dbo].[format_date]   </span></span></li>
<li>
<span>Script </span><span class="keyword">Date</span><span>: 05/12/2009 23:19:35 ******/  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span>IF EXISTS (</span><span class="keyword">SELECT</span><span> * </span><span class="keyword">FROM</span><span> sys.objects  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">WHERE</span><span> object_id = OBJECT_ID(N</span><span class="string">'[dbo].[format_date]'</span><span>)  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="op">AND</span><span> type </span><span class="op">in</span><span> (N</span><span class="string">'FN'</span><span>, N</span><span class="string">'IF'</span><span>, N</span><span class="string">'TF'</span><span>, N</span><span class="string">'FS'</span><span>, N</span><span class="string">'FT'</span><span>))  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">DROP</span><span> </span><span class="keyword">FUNCTION</span><span> [dbo].[format_date]  </span>
</li>
<li class="alt"><span> </span></li>
<li><span>GO  </span></li>
<li class="alt"><span> </span></li>
<li><span>/****** Object: UserDefinedFunction [dbo].[format_date]  </span></li>
<li class="alt"><span> </span></li>
<li>
<span>Script </span><span class="keyword">Date</span><span>: 05/12/2009 23:19:18 ******/  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">SET</span><span> ANSI_NULLS </span><span class="keyword">ON</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li><span>GO  </span></li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">SET</span><span> QUOTED_IDENTIFIER </span><span class="keyword">ON</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li><span>GO  </span></li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">SET</span><span> CONCAT_NULL_YIELDS_NULL </span><span class="keyword">OFF</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li><span>go  </span></li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">CREATE</span><span> </span><span class="keyword">function</span><span> [dbo].[format_date]  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span>(@inputdate datetime ,@format </span><span class="keyword">varchar</span><span>(500))  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">returns</span><span> </span><span class="keyword">varchar</span><span>(500)  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">as</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">begin</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @</span><span class="func">year</span><span> </span><span class="keyword">varchar</span><span>(4) </span><span class="comment">--YYYY </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @shortyear </span><span class="keyword">varchar</span><span>(4) </span><span class="comment">--Yr </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @quarter </span><span class="keyword">varchar</span><span>(4) </span><span class="comment">--QQ </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @</span><span class="func">month</span><span> </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--MM </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @week </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--WW </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @</span><span class="func">day</span><span> </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--DD </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @24hours </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--24HH </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @12hours </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--HH </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @minutes </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--MI </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @seconds </span><span class="keyword">varchar</span><span>(2) </span><span class="comment">--SS </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @milliseconds </span><span class="keyword">varchar</span><span>(3) </span><span class="comment">--MS </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @microseconds </span><span class="keyword">varchar</span><span>(6) </span><span class="comment">--MCS </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @nanoseconds </span><span class="keyword">varchar</span><span>(9) </span><span class="comment">--NS </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @dayname </span><span class="keyword">varchar</span><span>(15) </span><span class="comment">--DAY </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @monthname </span><span class="keyword">varchar</span><span>(15) </span><span class="comment">--MONTH </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @shortmonthname </span><span class="keyword">varchar</span><span>(15) </span><span class="comment">--MON </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @AMPM </span><span class="keyword">varchar</span><span>(15) </span><span class="comment">--AMPM </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @TZ </span><span class="keyword">varchar</span><span>(15) </span><span class="comment">--TZ </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @UNIXPOSIX </span><span class="keyword">varchar</span><span>(15) </span><span class="comment">--UNIXPOSIX </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="comment">--UCASE </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="comment">--LCASE </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">declare</span><span> @formatteddate </span><span class="keyword">varchar</span><span>(500)  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="comment">--Assign current date and time to </span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span>if (@inputdate </span><span class="keyword">is</span><span> </span><span class="op">NULL</span><span> </span><span class="op">or</span><span> @inputdate =</span><span class="string">''</span><span>)  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">begin</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">set</span><span> @inputdate = getdate()  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">end</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span>if (@format </span><span class="keyword">is</span><span> </span><span class="op">NULL</span><span> </span><span class="op">or</span><span> @format =</span><span class="string">''</span><span>)  </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">begin</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">set</span><span> @format =</span><span class="string">'YYYY-MM-DD 12HH:MI:SS AMPM'</span><span> </span>
</li>
<li class="alt"><span> </span></li>
<li>
<span class="keyword">end</span><span> </span>
</li>
</ol>