写了个函数,办法比较笨,不过效果是达到了
CREATE FUNCTION ConvertDateTimeToChinese
(
@DateTime DATETIME
)
RETURNS NVARCHAR(20)
AS
BEGIN
-- 返回的大写中文字符串
DECLARE @Chinese NVARCHAR(20)
DECLARE @Str NVARCHAR(20)
SET @Chinese = ''
-- 将日期转换成 2000-08-11 00:00:00 格式的字符串
SET @Str = CONVERT(VARCHAR(20),@DateTime,120)
-- 将'-'替换成'年',' '替换成'日',':'替换成'时'
SET @Str = REPLACE(@Str,'-','年')
SET @Str = REPLACE(@Str,' ','日')
SET @Str = REPLACE(@Str,':','时')
-- 将第二个'年'替换成'月',第二个'时'替换成'分'
SET @Str = STUFF(@Str,8,1,'月')
SET @Str = STUFF(@Str,17,1,'分')
DECLARE @Len INT
DECLARE @i INT
DECLARE @Num NVARCHAR(1)
SET @Len = LEN(@Str)
SET @i = 1
WHILE(@i <= @Len)
BEGIN
SET @Num = SUBSTRING(@Str,@i,1)
SET @Num = CASE @Num WHEN '0' THEN '零'
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 @Num END
SET @Chinese = @Chinese + @Num
SET @i = @i + 1
END
SET @Chinese = @Chinese + '秒'
RETURN @Chinese
END
调用上面创建的函数:
SELECT DBO.ConvertDateTimeToChinese('2011-08-08 00:00:00')
返回结果:
二零一一年零八月零八日零零时零零分零零秒 |