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

View Code

调用：

select [dbo].[fn_convertDateToChineseDX]('2017/11/08')

结果：

 

转载于:https://www.cnblogs.com/zjfblog/p/7804564.html