SQL函数-出票日期的规范写法
支票的日期填写方法:逢10日,20日,30日,都要在前面加零,如零壹拾日,零贰拾日,零叁拾日,因为不这样写,这些日子后面还可以填数字,有可能造成人为漏洞,而导致支票无法兑付,银行也会提醒客户补填或直接退票。
以下通过SQL2000自定义函数实现:
if exists(select name from sysobjects where name ='f_DateOfChinese' ) drop function f_DateOfChinese; go create function f_DateOfChinese(@date datetime) returns varchar(40) as begin -- 环境: sql2000 -- 《经济法》出票日期的规范写法 -- charset= GB3212 -- 2021年2月10日;转为:贰零贰壹年零贰月零壹拾日 declare @year int; declare @yearStr varchar(10); declare @month int; declare @monthStr varchar(10); declare @day int; declare @dayStr varchar(10); declare @arraylist varchar(20); declare @arraylist2 varchar(20); declare @i int; declare @m int; declare @d int; declare @day1 int; declare @len int; declare @num int; declare @word varchar(10); declare @getdate datetime; begin set @getdate = @date ; -- '2021-10-31'; set @arraylist ='零壹贰叁肆伍陆柒捌玖'; SET @arraylist2='整拾佰仟万拾佰仟亿拾佰仟'; set @year = year(@getdate); set @i= 1; set @yearStr = ''; set @word=''; -- 1): 转换年份 while @i<=4 begin set @num = cast( substring(cast(@year as varchar(10)),@i,1) as int); -- print str(@num) set @word = substring(@arraylist,@num+1,1); set @yearStr = @yearStr +@word; set @i = @i + 1; end; SET @yearStr = @yearStr+'年'; -- print @yearStr; set @month = month(@getdate); -- print str(@month); --- 2)处理月份 set @monthStr = ''; set @m=1; SELECT @MONTHSTR= (CASE WHEN 2< @MONTH AND @month<10 THEN substring(@arraylist,@month+1,1)+'月' WHEN @MONTH<=2 THEN '零'+ substring(@arraylist,@month+1,1)+'月' WHEN @MONTH=10 THEN '零壹拾月' WHEN @MONTH=11 THEN '壹拾壹月' WHEN @MONTH=12 THEN '壹拾贰月' -- set @m = @m+1; ELSE '' END) ; --print @yearStr+@monthStr; -- 3):处理"日"的部分 set @day = day(@getdate); set @len = len(@day); set @d = 1; set @dayStr = '日'; while @d <= @len begin set @day1 = cast( substring( cast(@day as varchar(4)),@len-@d+1,1) as varchar(10)); -- print str(@day1); select @dayStr =( substring(@arraylist,@day1+1,1))+ ( substring(@arraylist2,@d,1))+@dayStr; set @d = @d+1; end; set @dayStr = replace(@dayStr,'整',''); set @dayStr = replace(@dayStr,'零',''); -- print replace(@dayStr,'整',''); -- print replace(@dayStr,'零',''); if @day<10 set @dayStr = '零'+ @dayStr; else if (@day%10 =0 ) --余数,求模 mod() set @dayStr= '零'+ @dayStr; else set @dayStr = @dayStr; end; return( @yearStr + @monthStr + @dayStr); end go -- select dbo.f_DateOfChinese( '2020-10-20') as dateofChinese; --贰零贰零年零壹拾月零贰拾日 -- 贰零贰零年壹拾贰月零贰拾日
透过程序化理解“出票日期的规范写法”。
优质生活从拆开始