日期转换

[millsyys2006]

在SQL中如何把2004-12-29这种日期转换为二00四年十二月二十九日?

[millsyys2006]

在SQL中如何把2004-12-29这种日期转换为二零零四年十二月二十九日?

[wzy_love_sly]

用其他语言去转

[wzy_love_sly]

CREATE FUNCTION ChangeBigSmall
(@ChangeMoney money) 
RETURNS VarChar(100) AS  

BEGIN
    Declare    @String1    char(20)
    Declare    @String2    char(30)
    Declare    @String4    Varchar(100)
    Declare @String3     Varchar(100)    --从原A值中取出的值
    Declare @i         int        --循环变量
    Declare @J         Int        --A的值乘以100的字符串长度
    Declare @Ch1         Varchar(100)    --数字的汉语读法
    Declare @Ch2         Varchar(100)    --数字位的汉字读法
    Declare @Zero         Int        --用来计算连续有几个零
    Declare    @Returnvalue    VarChar(100)

    Select  @Returnvalue = ''
    Select     @String1 = '零壹贰叁肆伍陆柒捌玖'
    Select    @String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分'

    Select @String4 = Cast(@ChangeMoney*100 as int)   

    select @J=len(cast((@ChangeMoney*100) as int))

    Select @String2=Right(@String2,@J)

    Select    @i = 1

    while    @i<= @j Begin

        Select @String3 = Substring(@String4,@i,1)

        if @String3<>'0' Begin

            Select     @Ch1 = Substring(@String1, Cast(@String3 as Int) + 1, 1)
            Select    @Ch2 = Substring(@String2, @i, 1)
            Select    @Zero = 0                    --表示本位不为零
        end
        else Begin
            If (@Zero = 0) Or (@i = @J - 9) Or (@i = @J - 5) Or (@i = @J - 1)
                        Select @Ch1 = '零'
                    Else
                        Select @Ch1 = ''

                    Select @Zero = @Zero + 1             --表示本位为0
                       
            --如果转换的数值需要扩大，那么需改动以下表达式 I 的值。
            Select @Ch2 = ''

                If @i = @J - 10  Begin
                        Select @Ch2 = '亿'
                        Select @Zero = 0
            end
                   
            If @i = @J - 6 Begin
                        Select @Ch2 = '万'
                        Select @Zero = 0
            end
                   
            if @i = @J - 2 Begin
                        Select @Ch2 = '元'
                        Select @Zero = 0
            end
                   
            If @i = @J
                        Select @Ch2 = '整'
                       
        end   

        Select @Returnvalue = @Returnvalue + @Ch1 + @Ch2

        select @i = @i+1
    end

    --最后将多余的零去掉
    If CharIndex('仟仟',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '仟仟', '仟')

    If CharIndex('佰佰',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '佰佰', '佰')

        If CharIndex('零元',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零元', '元')
   
        If CharIndex('零万',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零万', '万')
  
        If CharIndex('零亿',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零亿', '亿')
   
        If CharIndex('零整',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零整', '整')
   
    If CharIndex('零佰',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零佰', '零')

    If CharIndex('零仟',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零仟', '零')

    If CharIndex('元元',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '元元', '元')

    return @Returnvalue

END

select dbo.ChangeBigSmall(1231212.543)

壹佰贰拾叁万壹仟贰佰壹拾贰元伍角肆分

[pt1314917]

create function f_os(@dt datetime)
returns varchar(50)
as
begin
	declare @dt1 varchar(8),@dt2 varchar(50)
	set @dt1=convert(varchar(8),@dt,112)
	set @dt2=replace(replace(replace(replace(replace(replace(replace(replace(replace(left(@dt1,4),'1','一'),'2','二'),'3','三'),'4','四'),'5','五'),'6','六'),'7','七'),'8','八'),'9','九')+'年'
	set @dt1=right(@dt1,4)
	set @dt2=@dt2+case left(@dt1,1) when '1' then '十' when '0' then '' end
	+case substring(@dt1,2,1) when '1' then '一' when '2' then '二' when '3' then '三' when '4' then '四'
	when '5' then '五' when '6' then '六' when '7' then '七' when '8' then '八' when '9' then '九' else '' end+'月'
	
	set @dt1=right(@dt1,2)
	set @dt2=@dt2+case left(@dt1,1) when '1' then '十' when '2' then '二十' when '3' then '三十' when '0' then '' end
	+case substring(@dt1,2,1) when '1' then '一' when '2' then '二' when '3' then '三' when '4' then '四'
	when '5' then '五' when '6' then '六' when '7' then '七' when '8' then '八' when '9' then '九' else '' end+'日'
	return @dt2
end


select dbo.f_os('2004-12-29')

[wzy_love_sly]

create table date(a varchar(10),h varchar(10))
insert into date select '0','零'
insert into date select '1','一'
insert into date select '2','二'
insert into date select '3','三'
insert into date select '4','四'
insert into date select '5','五'
insert into date select '6','六'
insert into date select '7','七'
insert into date select '8','八'
insert into date select '9','九'

create function g_date(@dt varchar(20),@dttype varchar(20))
returns varchar(20)
as
begin
declare @sql varchar(8000)
select @sql=isnull(@sql+'','')+h from (
select * from date a left join (
select substring(@dt,1,1) as dt,1 as orderid union all select substring(@dt,2,1) as dt ,2 as orderid union all select substring(@dt,3,1) as dt ,3 as orderid union all select substring(@dt,4,1) as dt,4 as orderid) b
on a.a=b.dt where b.orderid is not null )t order by orderid
while(left(@sql,1)='零' and @dttype<>'年')
set @sql=stuff(@sql,1,1,'')
if @dttype<>'年' and len(@sql)=2
if left(@sql,1)='一'
set @sql='十'+right(@sql,1)
else
set @sql=left(@sql,1)+'十'+right(@sql,1)
return @sql+@dttype
end


select dbo.g_date('2008','年')+dbo.g_date('02','月')+dbo.g_date('16','日') as '日期'

二零零八年二月十六日

[wzy_love_sly]

...

[conan304]

从网上copy的，呵呵

--2005-04-11 compile By ddh
------ 存储过程 ------
create procedure convertDateToChinese
@vdate datetime,@ChineseDateStr varchar(50) out 
as
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.convertNumToChinese(substring(@vYear,1,1))
set @temstr = @temstr + dbo.convertNumToChinese(substring(@vYear,2,1))
set @temstr = @temstr + dbo.convertNumToChinese(substring(@vYear,3,1))
set @temstr = @temstr + dbo.convertNumToChinese(substring(@vYear,4,1))
set @temstr = @temstr + '年'

-- 开始计算月份
if substring(@vMonth,1,1) = '0' 
set @temstr = @temstr + dbo.convertNumToChinese(substring(@vMonth,2,1)) 
else
begin
 if substring(@vMonth,2,1) = '0' 
   set @temstr = @temstr + '十'
 else
   set @temstr = @temstr +'十'+ dbo.convertNumToChinese(substring(@vMonth,2,1))
end

set @temstr = @temstr + '月'

-- 开始计算日期

if convert(int,@vDay) < 10  
  set @temstr = @temstr +  dbo.convertNumToChinese(substring(@vDay,1,1))
else
begin
  if substring(@vDay,2,1) = '0' 
    begin  
     if  substring(@vDay,1,1)<> '1'
       set  @temstr = @temstr +  dbo.convertNumToChinese(substring(@vDay,1,1)) + '十' 
      else
        set @temstr = @temstr +  '十' 
     end
  else
    begin
     if substring(@vDay,1,1) <> '1' 
       set @temstr = @temstr  +  dbo.convertNumToChinese(substring(@vDay,1,1)) + '十' + dbo.convertNumToChinese(substring(@vDay,2,1))
     else 
        set @temstr = @temstr + '十' + dbo.convertNumToChinese(substring(@vDay,2,1))
    end
end
set @temstr = @temstr +  '日'   
print @temstr
set @ChineseDateStr = @temstr

------ 配合相关函数 ------
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

declare @ChineseDateStr varchar(50),@date datetime
set @date=getdate()
exec convertDateToChinese @date,@ChineseDateStr output
/*
二零零八年四月八日
*/