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 函数，如下所示。

/****** Object: UserDefinedFunction [dbo].[format_date]   

Script Date: 05/12/2009 23:19:35 ******/  

 

IF EXISTS (SELECT * FROM sys.objects  

 

WHERE object_id = OBJECT_ID(N'[dbo].[format_date]')  

 

AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))  

 

DROP FUNCTION [dbo].[format_date]  

 
GO  
 
/****** Object: UserDefinedFunction [dbo].[format_date]  
 

Script Date: 05/12/2009 23:19:18 ******/  

 

SET ANSI_NULLS ON 

 
GO  
 

SET QUOTED_IDENTIFIER ON 

 
GO  
 

SET CONCAT_NULL_YIELDS_NULL OFF 

 
go  
 

CREATE function [dbo].[format_date]  

 

(@inputdate datetime ,@format varchar(500))  

 

returns varchar(500)  

 

as 

 

begin 

 

declare @year varchar(4) --YYYY  

 

declare @shortyear varchar(4) --Yr  

 

declare @quarter varchar(4) --QQ  

 

declare @month varchar(2) --MM  

 

declare @week varchar(2) --WW  

 

declare @day varchar(2) --DD  

 

declare @24hours varchar(2) --24HH  

 

declare @12hours varchar(2) --HH  

 

declare @minutes varchar(2) --MI  

 

declare @seconds varchar(2) --SS  

 

declare @milliseconds varchar(3) --MS  

 

declare @microseconds varchar(6) --MCS  

 

declare @nanoseconds varchar(9) --NS  

 

declare @dayname varchar(15) --DAY  

 

declare @monthname varchar(15) --MONTH  

 

declare @shortmonthname varchar(15) --MON  

 

declare @AMPM varchar(15) --AMPM  

 

declare @TZ varchar(15) --TZ  

 

declare @UNIXPOSIX varchar(15) --UNIXPOSIX  

 

--UCASE  

 

--LCASE  

 

declare @formatteddate varchar(500)  

 

--Assign current date and time to  

 

if (@inputdate is NULL or @inputdate ='')  

 

begin 

 

set @inputdate = getdate()  

 

end 

 

if (@format is NULL or @format ='')  

 

begin 

 

set @format ='YYYY-MM-DD 12HH:MI:SS AMPM' 

 

end