declare @t datetime,@s varchar(30) set @t = '2008-6-6 15:30:30'select @s = convert(varchar,@t ,120) select @s = left(@s,4) + '年'+substring(@s,6,2)+ '月' + substring(@s,9,2) + case when cast(substring(@s,12,2) as int ) > 12 then ' 下午 '+ right( '00'+ltrim(cast(substring(@s,12,2) as int ) - 12) ,2 ) else '上午' + substring(@s,12,2) end + right(@s,6)select @s/* ------------------------------ 2008年06月06 下午 03:30:30(所影响的行数为 1 行) */
declare @t varchar(30) set @t = '2005年6月6日'--while (patindex('%[0-9]%',@t) > 0) --begin set @t = replace(replace(replace(replace(replace(replace(@t,'1','一'),'2','二'),'3','三'),'0','O'),'5','五'),'6','六')select @t/* ------------------------------ 二OO五年六月六日(所影响的行数为 1 行)*/
declare @dt varchar(50) set @dt='2008-6-6 15:30:30' select datename(yy,@dt)+'年'+datename(mm,@dt)+'月'+datename(dd,@dt)+'日'+case when datepart(hh,@dt)>12 then '下午' else '上午' end +right(@dt,8)2008年06月6日下午15:30:30
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)) endset @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 测试 exec convertDateToChinese '2008-06-11 13:30','' 2008 06 v11 二零零八年六月十一日
create proc proc_enreplace @dt nvarchar(50) as begin declare @e nvarchar(10),@n nvarchar(10),@index int,@result varchar(50) select @e='0123456789',@n='零一二三四五六七八九',@index=1 while @index<=len(@dt) begin declare @s nvarchar(1) set @s=substring(@dt,@index,1) if charindex(@s,@e)>0 set @result=isnull(@result,'')+substring(@n,charindex(@s,@e),1) else set @result=isnull(@result,'')+@s set @index=@index+1 end select @result endexec proc_enreplace '2005年6月6日'二零零五年六月六日
declare @t datetime set @t='2008-6-6 15:30:30' select ltrim(year(@t))+'年'+ltrim(month(@t))+'月'+ltrim(day(@t))+'日 ' +case when datepart(hh,@t)>=12 then '下午 '+ right('00'+ltrim(datepart(hh,@t)-12),2) else '上午 '+right('00'+ltrim(datepart(hh,@t)),2) end +right(convert(varchar(10),@t,108),6)---结果: 2008年6月6日 下午 03:30:30
学习了,都是好东西! 我也来个declare @dt datetime set @dt=getdate() select rtrim(year(@dt))+'年'+rtrim(month(@dt))+'月'+rtrim(day(@dt))+'日 ' +case when datepart(hh,@dt)>12 then '下午 '+rtrim(datepart(hh,@dt)-12)+right(convert(varchar(20),@dt,120),6) else '上午 '+right(convert(varchar(20),@dt,120),8) end cndate
set @t = '2008-6-6 15:30:30'select @s = convert(varchar,@t ,120)
select @s = left(@s,4) + '年'+substring(@s,6,2)+ '月' + substring(@s,9,2)
+ case when cast(substring(@s,12,2) as int ) > 12 then ' 下午 '+ right( '00'+ltrim(cast(substring(@s,12,2) as int ) - 12) ,2 ) else '上午' + substring(@s,12,2) end
+ right(@s,6)select @s/*
------------------------------
2008年06月06 下午 03:30:30(所影响的行数为 1 行)
*/
set @t = '2005年6月6日'--while (patindex('%[0-9]%',@t) > 0)
--begin
set @t = replace(replace(replace(replace(replace(replace(@t,'1','一'),'2','二'),'3','三'),'0','O'),'5','五'),'6','六')select @t/*
------------------------------
二OO五年六月六日(所影响的行数为 1 行)*/
set @dt='2008-6-6 15:30:30'
select datename(yy,@dt)+'年'+datename(mm,@dt)+'月'+datename(dd,@dt)+'日'+case when datepart(hh,@dt)>12 then '下午' else '上午' end +right(@dt,8)2008年06月6日下午15:30:30
@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))
endset @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 测试
exec convertDateToChinese '2008-06-11 13:30',''
2008
06
v11
二零零八年六月十一日
@dt nvarchar(50)
as
begin
declare @e nvarchar(10),@n nvarchar(10),@index int,@result varchar(50)
select @e='0123456789',@n='零一二三四五六七八九',@index=1
while @index<=len(@dt)
begin
declare @s nvarchar(1)
set @s=substring(@dt,@index,1)
if charindex(@s,@e)>0
set @result=isnull(@result,'')+substring(@n,charindex(@s,@e),1)
else
set @result=isnull(@result,'')+@s
set @index=@index+1
end
select @result
endexec proc_enreplace '2005年6月6日'二零零五年六月六日
set @t='2008-6-6 15:30:30'
select ltrim(year(@t))+'年'+ltrim(month(@t))+'月'+ltrim(day(@t))+'日 '
+case when datepart(hh,@t)>=12 then '下午 '+ right('00'+ltrim(datepart(hh,@t)-12),2)
else '上午 '+right('00'+ltrim(datepart(hh,@t)),2) end
+right(convert(varchar(10),@t,108),6)---结果:
2008年6月6日 下午 03:30:30
Set @date='2008-6-6 15:30:30'
Select
Rtrim(Year(@date))+'年'+
Rtrim(Month(@date))+'月'+
Rtrim(Day(@date))+'日 '+
Replace(Replace(Right(Convert(char(18),@date,100),2),'AM','上午'),'PM','下午')+
Convert(char(8),@date,108)/*2008年6月6日 下午 03:30:30*/
--2005年6月6日 改成 二OO五年六月六日
Declare @x nvarchar(64)
Set @x='2005年6月6日'Select
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(@x,
'1','一'),
'2','二'),
'3','三'),
'4','四'),
'5','五'),
'6','六'),
'7','七'),
'8','八'),
'9','九'),
'0','O')/*二OO五年六月六日*/
我也来个declare @dt datetime
set @dt=getdate()
select rtrim(year(@dt))+'年'+rtrim(month(@dt))+'月'+rtrim(day(@dt))+'日 '
+case when datepart(hh,@dt)>12 then '下午 '+rtrim(datepart(hh,@dt)-12)+right(convert(varchar(20),@dt,120),6)
else '上午 '+right(convert(varchar(20),@dt,120),8) end cndate