怎样创建一个命令函数来获得不同国家和应用程序所要求的大多数日期格式
今天领导让我导出17年至19年的XX消费记录。还要按月汇总...我也醉了。查了各种资料都是说用convert(),再用 left截断。后来终于在生产库里边找到了format_date（）函数。真不错，向无名英雄致敬！
这篇文章介绍了怎样创建一个命令函数来获得不同国家和应用程序所要求的大多数日期格式。
源码如下
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
--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)<>
begin
set @formatteddate=replace(@formatteddate,'ucase','')
set @formatteddate=upper(@formatteddate)
end
if charindex('lcase',@formatteddate)<>
begin
set @formatteddate=replace(@formatteddate,'lcase','')
set @formatteddate=lower(@formatteddate)
end
return @formatteddate
end
相关文章