SQLServer2008中的创建格式化日期(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