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