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

<OL class=dp-sql><LI class=alt><SPAN><SPAN>/****** Object: UserDefinedFunction [dbo].[format_date]   </SPAN></SPAN><LI><SPAN>Script </SPAN><SPAN class=keyword>Date</SPAN><SPAN>: 05/12/2009 23:19:35 ******/  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>IF EXISTS (</SPAN><SPAN class=keyword>SELECT</SPAN><SPAN> * </SPAN><SPAN class=keyword>FROM</SPAN><SPAN> sys.objects  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>WHERE</SPAN><SPAN> object_id = OBJECT_ID(N</SPAN><SPAN class=string>'[dbo].[format_date]'</SPAN><SPAN>)  </SPAN><LI class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><LI><SPAN class=keyword>DROP</SPAN><SPAN> </SPAN><SPAN class=keyword>FUNCTION</SPAN><SPAN> [dbo].[format_date]  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>GO  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>/****** Object: UserDefinedFunction [dbo].[format_date]  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>Script </SPAN><SPAN class=keyword>Date</SPAN><SPAN>: 05/12/2009 23:19:18 ******/  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>SET</SPAN><SPAN> ANSI_NULLS </SPAN><SPAN class=keyword>ON</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>GO  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>SET</SPAN><SPAN> QUOTED_IDENTIFIER </SPAN><SPAN class=keyword>ON</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>GO  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>SET</SPAN><SPAN> CONCAT_NULL_YIELDS_NULL </SPAN><SPAN class=keyword>OFF</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>go  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>CREATE</SPAN><SPAN> </SPAN><SPAN class=keyword>function</SPAN><SPAN> [dbo].[format_date]  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN>(@inputdate datetime ,@format </SPAN><SPAN class=keyword>varchar</SPAN><SPAN>(500))  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>returns</SPAN><SPAN> </SPAN><SPAN class=keyword>varchar</SPAN><SPAN>(500)  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>as</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>begin</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><LI><SPAN class=comment>--UCASE </SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=comment>--LCASE </SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>declare</SPAN><SPAN> @formatteddate </SPAN><SPAN class=keyword>varchar</SPAN><SPAN>(500)  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=comment>--Assign current date and time to </SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><LI><SPAN class=keyword>begin</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>set</SPAN><SPAN> @inputdate = getdate()  </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>end</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><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 class=alt><SPAN> </SPAN><LI><SPAN class=keyword>begin</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>set</SPAN><SPAN> @format =</SPAN><SPAN class=string>'YYYY-MM-DD 12HH:MI:SS AMPM'</SPAN><SPAN> </SPAN><LI class=alt><SPAN> </SPAN><LI><SPAN class=keyword>end</SPAN><SPAN> </SPAN></LI></OL>