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

--set all values   
set @year = convert(varchar(4),year(@inputdate))  
 
set @shortyear = right(@year,2)  
 
set @quarter = convert(varchar(1),datepart(QQ,(@inputdate)))  
 
set @month = right('0'+convert(varchar(2),month(@inputdate)),2)  
 
set @week = right('0'+convert(varchar(2),datepart(ww,(@inputdate))),2)  
 
set @day = right('0'+convert(varchar(2),day(@inputdate)),2)  
 
set @24hours = right('0'+convert(varchar(2),datepart(hh,@inputdate)),2)  
 
set @TZ = convert(varchar(10),datename(TZ,convert(varchar(20),@inputdate)))  
 
set @UNIXPOSIX = convert(varchar(15),datediff(ss,convert(datetime,'01/01/1970 00:00:000'),@inputdate))  
 
if datepart(hh,@inputdate) >12  
 
begin 
 
set @12hours = right('0'+convert(varchar(2),datepart(hh,@inputdate)) -12,2)  
 
end 
 
else 
 
begin 
 
set @12hours = right('0'+convert(varchar(2),datepart(hh,@inputdate)) ,2)  
 
end 
 
if datepart(hh,@inputdate) >11  
 
begin 
 
set @AMPM ='PM' 
 
end 
 
else 
 
begin 
 
set @AMPM ='AM' 
 
end 
 
set @minutes = right('0'+convert(varchar(2),datepart(n,@inputdate)),2)  
 
set @seconds = right('0'+convert(varchar(2),datepart(ss,@inputdate)),2)  
 
set @milliseconds = convert(varchar(3),datepart(ms,@inputdate))  
 
set @microseconds = convert(varchar(6),datepart(mcs,@inputdate))  
 
set @nanoseconds = convert(varchar(9),datepart(ns,@inputdate))  
 
set @dayname = datename(weekday,@inputdate)  
 
set @monthname = datename(mm,@inputdate)  
 
set @shortmonthname= left(datename(mm,@inputdate),3)  
 
set @formatteddate = @format  
 
set @formatteddate=replace(@formatteddate,'MONTH',@monthname)  
 
set @formatteddate=replace(@formatteddate,'MON',@shortmonthname)  
 
set @formatteddate=replace(@formatteddate,'AMPM',@AMPM)  
 
set @formatteddate=replace(@formatteddate,'YYYY',@year)  
 
set @formatteddate=replace(@formatteddate,'Yr',@shortyear)  
 
set @formatteddate=replace(@formatteddate,'QQ',@quarter)  
 
set @formatteddate=replace(@formatteddate,'WW',@week)  
 
set @formatteddate=replace(@formatteddate,'MM',@month)  
 
set @formatteddate=replace(@formatteddate,'DD',@Day)  
 
set @formatteddate=replace(@formatteddate,'24HH',@24hours)  
 
set @formatteddate=replace(@formatteddate,'12HH',@12hours)  
 
set @formatteddate=replace(@formatteddate,'Mi',@minutes)  
 
set @formatteddate=replace(@formatteddate,'SS',@seconds)  
 
set @formatteddate=replace(@formatteddate,'MS',@milliseconds)  
 
set @formatteddate=replace(@formatteddate,'MCS',@microseconds)  
 
set @formatteddate=replace(@formatteddate,'NS',@nanoseconds)  
 
set @formatteddate=replace(@formatteddate,'DAY',@dayname)  
 
set @formatteddate=replace(@formatteddate,'TZ',@TZ)  
 
set @formatteddate=replace(@formatteddate,'UNIXPOSIX',@UNIXPOSIX)  
 
if charindex('ucase',@formatteddate)<>0  
 
begin 
 
set @formatteddate=replace(@formatteddate,'ucase','')  
 
set @formatteddate=upper(@formatteddate)  
 
end 
 
if charindex('lcase',@formatteddate)<>0   
 
begin 
 
set @formatteddate=replace(@formatteddate,'lcase','')  
 
set @formatteddate=lower(@formatteddate)  
 
end 
 
return @formatteddate  
 
end 
 
GO