SQL常用自定义函数
--************************************将数字年月日变换成中文
CREATE FUNCTION ymdseq
(@ymd datetime)
RETURNS varchar(24)
AS
BEGIN
declare @x varchar(2),@y varchar(2),@yy varchar(4),@dd varchar(6),@z varchar(2),@e varchar(2),@r varchar(2),@t varchar(2)
set @e=case when left(datepart(year,@ymd),1)='1' then '一' when left(datepart(year,@ymd),1)='2' then '二' when left(datepart(year,@ymd),1)='3' then '三' when left(datepart(year,@ymd),1)='4' then '四'
when left(datepart(year,@ymd),1)='5' then '五' when left(datepart(year,@ymd),1)='6' then '六' when left(datepart(year,@ymd),1)='7' then '七' when left(datepart(year,@ymd),1)='8' then '八'
when left(datepart(year,@ymd),1)='9' then '九' end
set @z=case when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='1' then '一' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='2' then '二' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='3' then '三' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='4' then '四'
when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='5' then '五' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='6' then '六' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='7' then '七' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='8' then '八'
when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='9' then '九' when substring(cast(datepart(year,@ymd) as varchar(4)),2,1)='0' then '零' end
set @r=case when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='1' then '一' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='2' then '二' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='3' then '三' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='4' then '四'
when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='5' then '五' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='6' then '六' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='7' then '七' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='8' then '八'
when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='9' then '九' when substring(cast(datepart(year,@ymd) as varchar(4)),3,1)='0' then '零' end
set @t=case when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='1' then '一' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='2' then '二' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='3' then '三' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='4' then '四'
when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='5' then '五' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='6' then '六' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='7' then '七' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='8' then '八'
when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='9' then '九' when substring(cast(datepart(year,@ymd) as varchar(4)),4,1)='0' then '零' end
set @x=case when datepart(mm,@ymd) <10 then '0'+cast(datepart(mm,@ymd) as varchar(2))
else cast(datepart(mm,@ymd) as varchar(2)) end
set @yy=case when @x='01' then '一' when @x='02' then '二' when @x='03' then '三' when @x='04' then '四' when @x='05' then '五' when @x='06' then '六' when @x='07' then '七'
when @x='08' then '八' when @x='09' then '九' when @x='10' then '十' when @x='11' then '十一' when @x='12' then '十二' end
set @y=case when datepart(day,@ymd) <10 then '0'+cast(datepart(day,@ymd) as varchar(2))
else cast(datepart(day,@ymd) as varchar(2)) end
set @dd=case when @y='01' then '一' when @y='02' then '二' when @y='03' then '三' when @y='04' then '四' when @y='05' then '五' when @y='06' then '六' when @y='07' then '七'
when @y='08' then '八' when @y='09' then '九' when @y='10' then '十' when @y='11' then '十一' when @y='12' then '十二' when @y='13' then '十三' when @y='13' then '十三'
when @y='14' then '十四' when @y='15' then '十五' when @y='16' then '十六' when @y='17' then '十七' when @y='18' then '十八' when @y='19' then '十九' when @y='20' then '二十'
when @y='21' then '二十一' when @y='22' then '二十二' when @y='23' then '二十三' when @y='24' then '二十四' when @y='25' then '二十五' when @y='26' then '二十六' when @y='27' then '二十七'
when @y='28' then '二十八' when @y='29' then '二十九' when @y='30' then '三十' when @y='31' then '三十一'
end
RETURN
@e+@z+@r+@t+'年'+@yy+'月'+@dd+'日'
END
--**********************************将日期转化为'2007-7-1' 的格式
CREATE FUNCTION dat1
(@ymd datetime)
RETURNS varchar(13)
AS
BEGIN
RETURN CAST(YEAR(@ymd) AS varchar) +'-'+case when month(@ymd)<10 then '0'+CAST(month(@ymd) AS varchar)
else CAST(month(@ymd) AS varchar) end
+ '-' +case when DAY(@ymd)<10 then '0'+CAST(DAY(@ymd) AS varchar)
else CAST(DAY(@ymd) AS varchar) end
END
--**********************************取每个没字的拼音第一个字母**********************************
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
--**********************************格式为'200707'的月份减一**********************************
CREATE FUNCTION GZ_YM
(@GZ_YM VARCHAR(6))
RETURNS varchar(6)
AS
BEGIN
return
case when RIGHT(@GZ_YM,2)='01' then CAST(LEFT(@GZ_YM,4)-1 AS VARCHAR(4))+'12'
else @GZ_YM-1 end
END
--**********************************格式为'200707'的月份加一**********************************
CREATE FUNCTION GZ_YMj
(@GZ_YM VARCHAR(6))
RETURNS varchar(6)
AS
BEGIN
return
case when RIGHT(@GZ_YM,2)='12' then CAST(LEFT(@GZ_YM,4)+1 AS VARCHAR(4))+'01'
else @GZ_YM+1 end
END
--**********************************将时间转换为'08:11'**********************************
CREATE FUNCTION tim
(@ymd datetime)
RETURNS varchar(13)
AS
BEGIN
declare @x varchar(2)
set @x=case when datepart(mi,@ymd) <10 then '0'+cast(datepart(mi,@ymd) as varchar(2))
else cast(datepart(mi,@ymd) as varchar(2)) end
RETURN
case when @ymd is null
then ''
else
cast(datepart(hh,@ymd) as varchar(2))+':'+@x
end
END
--**********************************将'2007-5-1'的格式转换为'200705'**********************************
CREATE FUNCTION ym
(@ymd datetime)
RETURNS varchar(13)
AS
BEGIN
RETURN CAST(YEAR(@ymd) AS varchar)+case when month(@ymd)<10 then '0'+CAST(month(@ymd) AS varchar)
else CAST(month(@ymd) AS varchar) end
END
--**********************************将日期为15号之前的转换为上月，15号之后的转换为下月**********************************
CREATE FUNCTION ym15
(@ymd datetime)
RETURNS varchar(13)
AS
BEGIN
return case when datepart(day,@ymd)<=15 then dbo.ym(@ymd) else
case when datepart(month,@ymd)=12 then cast(cast(datepart(year,dateadd(year,1,@ymd)) as varchar(4))+cast('01' as varchar(2)) as varchar(6))
else cast(cast(datepart(year,@ymd) as varchar(4))+cast(dateadd(month,1,@ymd) as varchar(2)) as varchar(6)) end end
END
--**********************************将'2007-12-10'的格式转换为'20071201'**********************************
CREATE FUNCTION ymd
(@ymd datetime)
RETURNS varchar(8)
AS
BEGIN
declare @x varchar(2),@y varchar(2)
set @x=case when datepart(mm,@ymd) <10 then '0'+cast(datepart(mm,@ymd) as varchar(2))
else cast(datepart(mm,@ymd) as varchar(2)) end
set @y=case when datepart(day,@ymd) <10 then '0'+cast(datepart(day,@ymd) as varchar(2))
else cast(datepart(day,@ymd) as varchar(2)) end
RETURN
cast(datepart(yyyy,@ymd) as varchar(4))+@x+@y
END
--**********************************将'20071201' 的格式转换为'2007-12-01'**********************************
CREATE FUNCTION ymdate
(@ym varchar(8))
RETURNS datetime
AS
BEGIN
declare @x varchar(4),@y varchar(2),@z varchar(2),@d datetime
set @x=left(@ym,4)
set @y=substring(@ym,5,2)
set @z=right(@ym,2)
set @d=cast(@x+'-'+@y+'-'+@z as datetime)
return
@d
END
--**********************************当月除休息日设置 的天数**********************************
CREATE FUNCTION ymday
(@ym varchar(6))
RETURNS int
AS
BEGIN
declare @x int,@startd datetime,@endd datetime,@y int,@z int
set @x=case when right(@ym,2)='01' then 31
when right(@ym,2)='02' then
case when (left(@ym,4) % 4 = 0) and ((left(@ym,4) % 100 <> 0) or (left(@ym,4) % 400 = 0))
then 29
else 28
end
when right(@ym,2)='03' then 31
when right(@ym,2)='04' then 30
when right(@ym,2)='05' then 31
when right(@ym,2)='06' then 30
when right(@ym,2)='07' then 31
when right(@ym,2)='08' then 31
when right(@ym,2)='09' then 30
when right(@ym,2)='10' then 31
when right(@ym,2)='11' then 30
when right(@ym,2)='12' then 31
else 0
end
set @startd=cast(cast(left(@ym,4) as varchar(4))+'-'+cast(right(@ym,2) as varchar(2))+'-1' as datetime)
set @endd=cast(cast(left(@ym,4) as varchar(4))+'-'+cast(right(@ym,2) as varchar(2))+'-'+cast(@x as varchar(2)) as datetime)
set @y=0
while @startd<=@endd
begin
set @y=@y+case when datepart(weekday,@startd) in(7,1) then 1
else 0 end
set @startd=dateadd(dd,1,@startd)
end
set @z= @x-@y
return
@z
END
--**********************************将'2007-12-01' 转换为'2007年12月01日'**********************************
CREATE FUNCTION ymdse
(@ymd datetime)
RETURNS varchar(20)
AS
BEGIN
declare @x varchar(2),@y varchar(2)
set @x=case when datepart(mm,@ymd) <10 then '0'+cast(datepart(mm,@ymd) as varchar(2))
else cast(datepart(mm,@ymd) as varchar(2)) end
set @y=case when datepart(day,@ymd) <10 then '0'+cast(datepart(day,@ymd) as varchar(2))
else cast(datepart(day,@ymd) as varchar(2)) end
RETURN
cast(datepart(yyyy,@ymd) as varchar(4))+'年'+@x+'月'+@y+'日'
END
--**********************************将时间'0800' 转换为日期形式'1900-1-1 08:00:00.000'
CREATE FUNCTION ymtime
(@ym varchar(4))
RETURNS datetime
AS
BEGIN
declare @x varchar(2),@y varchar(2),@z varchar(2),@d datetime
set @x=left(@ym,2)
set @z=right(@ym,2)
set @d=cast('1900-1-1 '+@x+':'+@z as datetime)
return
@d
END
