SQL Server 汉语日期和"标准日期"的转换

[公司的日期格式为:yyyy-mm-dd,下面把这种日期称为标准日期] 简介: 一、把标准日期转换成汉语日期 二、把汉语日期转换成标准日期 三、把年月日去掉转成标准日期 =========================================================================== 一、把标准日期转换成汉语日期 -- select dbo.fn_DateToUpper('2007-12-29') -- Result: 二○○七年十二月二十九日 --Function="Name:转成汉语日期;Class:系统功能;Author:zhang yong jun;" ALTER                 function fn_DateToUpper ( @date  varchar(20)  --待转日期 ) returns nvarchar(20) as begin    declare @Ret  nvarchar(20) --返回值   ,@tempdt  datetime --临时日期   ,@year  nvarchar(4) --年   ,@month  nvarchar(2) --月   ,@day  nvarchar(3) --日    if(len(@date) > 4 and isdate(@date) = 1)  --传入年-月-日 begin   set @tempdt = @date   select    @year  = cast(year(@tempdt) as nvarchar(4))    ,@month = cast(month(@tempdt) as nvarchar(2))    ,@day = cast(day(@tempdt) as nvarchar(2)) end else if(isdate(@date + '-01') = 1) --传入年-月 begin   set @tempdt = @date + '-01'   select    @year  = cast(year(@tempdt) as nvarchar(4))    ,@month = cast(month(@tempdt) as nvarchar(2))    ,@day = '' end else if(isdate(@date + '-01-01') = 1) --传入年 begin --   set @tempdt = @date + '-01-01'   select    @year  = @date    ,@month = ''    ,@day = '' end else --  if(@year is null or len(@year) = 0) begin   return null end select @year = replace(@year, b, a) from (   select a = '○', b = '0' union all   select a = '一', b = '1' union all   select a = '二', b = '2' union all   select a = '三', b = '3' union all   select a = '四', b = '4' union all   select a = '五', b = '5' union all   select a = '六', b = '6' union all   select a = '七', b = '7' union all   select a = '八', b = '8' union all   select a = '九', b = '9'   ) tyear set @Ret = @year + N'年' if(len(@month) > 0) begin   select  @month = replace(@month, b, a)   from (    select a = '十二', b = '12' union all    select a = '十一', b = '11' union all    select a = '十', b = '10' union all    select a = '一', b = '1' union all    select a = '二', b = '2' union all    select a = '三', b = '3' union all    select a = '四', b = '4' union all    select a = '五', b = '5' union all    select a = '六', b = '6' union all    select a = '七', b = '7' union all    select a = '八', b = '8' union all    select a = '九', b = '9'    ) tmonth   set @Ret = @Ret + @month + N'月' end if(len(@day) > 0) begin   select  @day = replace(@day, b, a)   from (    select a = '三十一',  b = '31' union all    select a = '三十',  b = '30' union all    select a = '二十九',  b = '29' union all    select a = '二十八',  b = '28' union all    select a = '二十七',  b = '27' union all    select a = '二十六',  b = '26' union all    select a = '二十五',  b = '25' union all    select a = '二十四',  b = '24' union all    select a = '二十三',  b = '23' union all    select a = '二十二',  b = '22' union all    select a = '二十一',  b = '21' union all    select a = '二十',  b = '20' union all    select a = '十九', b = '19' union all    select a = '十八',  b = '18' union all    select a = '十七',  b = '17' union all    select a = '十六',  b = '16' union all    select a = '十五',  b = '15' union all    select a = '十四',  b = '14' union all    select a = '十三',  b = '13' union all    select a = '十二',  b = '12' union all    select a = '十一',  b = '11' union all    select a = '十',  b = '10' union all    select a = '九',  b = '9' union all    select a = '八',  b = '8' union all    select a = '七',  b = '7' union all    select a = '六',  b = '6' union all    select a = '五',  b = '5' union all    select a = '四',  b = '4' union all    select a = '三',  b = '3' union all    select a = '二',  b = '2' union all    select a = '一',  b = '1'    ) tday   set @Ret = @Ret + @day + N'日' end    return @Ret end 二、把汉语日期转换成标准日期 -- select dbo.fn_DateToLower('二○○七年十二月二十九日') -- Result: 2007-12-29 --Function="Name:转换汉语日期;Class:系统功能;Author:zhang yong jun;" ALTER                function fn_DateToLower ( @date nvarchar(20)  --汉语日期 ) returns varchar(20) as begin    declare @Ret varchar(20) --  set @Ret = null -- select @date = replace(@date, a, b) from (   select a = '年',  b = '-' union all   select a = '月',  b = '-' union all   select a = '日',  b = ''  union all   select a = '三十一',  b = '31' union all   select a = '三十',  b = '30' union all   select a = '二十九',  b = '29' union all   select a = '二十八',  b = '28' union all   select a = '二十七',  b = '27' union all   select a = '二十六',  b = '26' union all   select a = '二十五',  b = '25' union all   select a = '二十四',  b = '24' union all   select a = '二十三',  b = '23' union all   select a = '二十二',  b = '22' union all   select a = '二十一',  b = '21' union all   select a = '二十',  b = '20' union all   select a = '十九', b = '19' union all   select a = '十八',  b = '18' union all   select a = '十七',  b = '17' union all   select a = '十六',  b = '16' union all   select a = '十五',  b = '15' union all   select a = '十四',  b = '14' union all   select a = '十三',  b = '13' union all   select a = '十二',  b = '12' union all   select a = '十一',  b = '11' union all   select a = '十',  b = '10' union all   select a = '九',  b = '9' union all   select a = '八',  b = '8' union all   select a = '七',  b = '7' union all   select a = '六',  b = '6' union all   select a = '五',  b = '5' union all   select a = '四',  b = '4' union all   select a = '三',  b = '3' union all   select a = '二',  b = '2' union all   select a = '一',  b = '1' union all   select a = '○',  b = '0'   ) t if right(@date,1) = '-' begin   set @date = substring(@date, 1, len(@date) - 1) end if (len(@date) > 4 and isdate(@date) = 1) begin   set @Ret = convert(varchar(10), cast(@date as datetime), 120) end else if(isdate(@date + '-01') = 1) begin   set @Ret = left(convert(varchar(10), cast(@date + '-01' as datetime), 120), 7) end else if(isdate(@date + '-01-01') = 1)   begin     set @Ret = @date end return @Ret end 三、把年月日去掉转成标准日期 有的时候我们从别地方取到的日期类似这种格式：2008年10月27日 需要把年月日替换掉才能保存到字段类型为DateTime的字段， 针对这种情况写一个简单的函数来实现转换 -- select dbo.fn_DateFormat('2008年10月17日') -- Result: 2008-10-17 00:00:00.000 --Function="Name:格式化日期,非日期返回null;Class:系统功能;Author:zhang yong jun;" ALTER          function fn_DateFormat ( @date  nvarchar(50)  --待转日期 ) returns datetime as begin    declare @Ret  datetime --返回值      select @date = replace(@date, a, b) from ( --   select a = ' ', b = '' union all --半角空格 --   select a = '　', b = '' union all --全角空格   select a = '年', b = '-' union all   select a = '月', b = '-' union all   select a = '日', b = ' ' union all   select a = '号', b = ' ' union all   select a = '时', b = ':' union all   select a = '点', b = ':' union all   select a = '分', b = ':' union all   select a = '秒', b = ''   ) dt if right(@date, 1) = ':' begin   set @date = substring(@date, 1, len(@date) - 1) end --  set @date = rtrim(@date) if(isdate(@date) = 1) begin   set @Ret = @date end return @Ret end