sqlserver年月日转汉字大写--自定义函数--繁体
两个自定义函数结合
函数一:
create function convertNumToChinese (@instr varchar(2)) returns varchar(2) as begin declare @temStr varchar(2) if @instr = '1' set @temStr = '壹' if @instr ='2' set @temStr = '贰' if @instr ='3' set @temStr = '叁' if @instr ='4' set @temStr = '肆' if @instr ='5' set @temStr = '伍' if @instr ='6' set @temStr = '陆' if @instr ='7' set @temStr = '柒' if @instr ='8' set @temStr = '捌' if @instr ='9' set @temStr = '玖' if @instr ='0' set @temStr = '零' return @temstr end
函数二:
create function [dbo].[fn_convertDateToChineseDX] (@vdate datetime) returns varchar(50) AS BEGIN declare @vYear varchar(20) declare @vMonth varchar(20) declare @vDay varchar(20) declare @temstr varchar(100) set @temstr = '' set @vYear = datename(yyyy,@vdate) --print @vyear set @vMonth = datename(mm,@vdate) --print @vMonth set @vDay = datename(dd,@vdate) --print 'v'+@vDay -- 开始计算年份 set @temstr = dbo.convertNumToChineseDX(substring(@vYear,1,1)) set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vYear,2,1)) set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vYear,3,1)) set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vYear,4,1)) set @temstr = @temstr + '年' -- 开始计算月份 if substring(@vMonth,1,1) = '0' set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vMonth,2,1)) else begin if substring(@vMonth,2,1) = '0' set @temstr = @temstr + '拾' else set @temstr = @temstr +'壹拾'+ dbo.convertNumToChineseDX(substring(@vMonth,2,1)) end set @temstr = @temstr + '月' -- 开始计算日期 if convert(int,@vDay) < 10 set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vDay,1,1)) else begin if substring(@vDay,2,1) = '0' begin if substring(@vDay,1,1)<> '1' set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vDay,1,1)) + '拾' else set @temstr = @temstr + '拾' end else begin if substring(@vDay,1,1) <> '1' set @temstr = @temstr + dbo.convertNumToChineseDX(substring(@vDay,1,1)) + '拾' + dbo.convertNumToChineseDX(substring(@vDay,2,1)) else set @temstr = @temstr + '壹拾' + dbo.convertNumToChineseDX(substring(@vDay,2,1)) end end set @temstr = @temstr + '日' RETURN @temstr END
调用:
select [dbo].[fn_convertDateToChineseDX]('2017/11/08')
结果: