SQL Server判断是否满足日期格式(YYYYMMDD)以及中文等判断:
在做sql数据的正确性审核中,需要判断数据是否满足日期格式,网上找不到相关的资料,于是自己花了半天写了一个简单的函数
具体思路:
1.判断字符串是否8位
2.如果是8位,通过datalength判断是否8位,如果不是8位,说明字符串中存在汉字或者不可见字符
3.通过Ascii码对8位中的每一位字符进行判断,目的是判断是否数字(按照AsciI码可以避免不可见字符)
4.如果符合上述3中条件,说明字符是8位的数字,判断是否正常日期
1 ALTER FUNCTION [dbo].[isDataYYYYMMDD]( @Number nvarchar(50)) 2 RETURNS int 3 AS 4 begin 5 if LEN(ltrim(rtrim(@Number)))<>8 or CHARINDEX('.',@Number)>0 or CHARINDEX('#',@Number)>0 or CHARINDEX('*',@Number)>0 or CHARINDEX('-',@Number)>0 or CHARINDEX('',@Number)>0 or CHARINDEX('/',@Number)>0 6 begin 7 return 1 8 end 9 else if datalength(ltrim(rtrim(@Number)))<> 8 10 if ascii(left(@Number,1))> 57 or ascii(left(@Number,1))<48 11 or ascii(right(left(@Number,2),1))> 57 or ascii(right(left(@Number,2),1))<48 12 or ascii(right(left(@Number,3),1))> 57 or ascii(right(left(@Number,3),1))<48 13 or ascii(right(left(@Number,4),1))> 57 or ascii(right(left(@Number,4),1))<48 14 or ascii(right(left(@Number,5),1))> 57 or ascii(right(left(@Number,5),1))<48 15 or ascii(right(left(@Number,6),1))> 57 or ascii(right(left(@Number,6),1))<48 16 or ascii(right(left(@Number,7),1))> 57 or ascii(right(left(@Number,7),1))<48 17 or ascii(right(left(@Number,8),1))> 57 or ascii(right(left(@Number,8),1))<48 18 begin 19 return 1 20 end 21 else 22 begin 23 return 0 24 end 25 else 26 begin 27 if DATENAME ( year ,@Number)>2017 28 begin 29 return 1 30 end 31 else if DATENAME ( month ,@Number)>12 or DATENAME ( month ,@Number)<1 32 begin 33 return 1 34 end 35 else if DATENAME ( DAY ,@Number)>31 or DATENAME ( DAY ,@Number)<1 36 begin 37 return 1 38 end 39 end 40 return 0 41 end
2.格式化为YYYY-MM-DD
去掉0:20170909:2017-9-9
1 ALTER FUNCTION [dbo].[formatTime]( @Number nvarchar(500)) 2 RETURNS nvarchar(500) 3 --返回-1,表示字符串存在问题 4 --else返回其计算结果 5 AS 6 begin 7 --判断非法字符 8 if PATINDEX('%[^0123456789]%', @Number)>0 9 begin 10 return @Number 11 end 12 --如果长度小于4位,eg:2004,200,就直接返回 13 else if len(@Number)<=4 14 begin 15 return @Number 16 end 17 --如果长度为5位,eg:20045,返回2004-5 18 else if len(@Number)=5 19 begin 20 if right(@Number,1)='0' 21 begin 22 return left(@Number,4) 23 end 24 else 25 begin 26 return left(@Number,4)+'-'+right(@Number,1) 27 end 28 end 29 --如果长度为6位 30 else if len(@Number)=6 31 begin 32 if right(@Number,2)<='12' 33 begin 34 if left(right(@Number,2),1)='0' 35 begin 36 return left(@Number,4)+'-'+right(@Number,1) 37 end 38 else 39 begin 40 return left(@Number,4)+'-'+right(@Number,2) 41 end 42 end 43 else 44 begin 45 if right(@Number,1)='0' 46 begin 47 return left(@Number,4)+'-'+left(right(@Number,2),1) 48 end 49 else 50 begin 51 return left(@Number,4)+'-'+left(right(@Number,2),1)+'-'+right(@Number,1) 52 end 53 end 54 end 55 --如果长度为7位 56 else if len(@Number)=7 57 begin 58 --第5,6位组合起来,小于12 eg:2015111 2015-11-1 59 if left(right(@Number,3),2)<='12' 60 begin 61 if left(right(@Number,3),1)='0' and right(@Number,1)<>'0' 62 begin 63 return left(@Number,4)+'-'+left(right(@Number,2),1)+'-'+right(@Number,1) 64 end 65 else if left(right(@Number,3),1)='0' and right(@Number,1)='0' 66 begin 67 return left(@Number,4)+'-'+left(right(@Number,2),1) 68 end 69 else if left(right(@Number,3),1)<>'0' and right(@Number,1)='0' 70 begin 71 return left(@Number,4)+'-'+left(right(@Number,3),2) 72 end 73 else 74 begin 75 return left(@Number,4)+'-'+left(right(@Number,3),2)+'-'+right(@Number,1) 76 end 77 end 78 --第5,6位组合起来大于12,且第6,7位小于31, eg:2015211 2015-2-11 79 else if left(right(@Number,3),2)>'12' and right(@Number,2)<='31' 80 begin 81 if right(@Number,2)='00' 82 begin 83 return left(@Number,4)+'-'+left(right(@Number,3),1) 84 end 85 else if left(right(@Number,2),1)='0' 86 begin 87 return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,1) 88 end 89 end 90 --第5,6位组合起来大于12,且第6,7位大于31, eg:2015235 不合理 91 else 92 return @Number 93 end 94 --如果长度为8位 95 else if len(@Number)=8 96 begin 97 if left(right(@Number,4),2)<='12' and right(@Number,2)<='31' 98 begin 99 if left(right(@Number,4),2)='00' or right(@Number,2)='00' 100 begin 101 return @Number 102 end 103 else if left(right(@Number,4),1)='0' and left(right(@Number,2),1)='0' 104 begin 105 return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,1) 106 end 107 else if left(right(@Number,4),1)='0' and left(right(@Number,2),1)<>'0' 108 begin 109 return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,2) 110 end 111 else if left(right(@Number,4),1)<>'0' and left(right(@Number,2),1)='0' 112 begin 113 return left(@Number,4)+'-'+left(right(@Number,4),2)+'-'+right(@Number,1) 114 end 115 else 116 return left(@Number,4)+'-'+left(right(@Number,4),2)+'-'+right(@Number,2) 117 end 118 else --不合法 119 begin 120 return @Number 121 end 122 end 123 else --不合法 124 begin 125 return @Number 126 end 127 return '0' 128 end
不去0:20170909:2017-09-09
1 ALTER FUNCTION [dbo].[formatTime1]( @Number nvarchar(500)) 2 RETURNS nvarchar(500) 3 --返回-1,表示字符串存在问题 4 --else返回其计算结果 5 AS 6 begin 7 --判断非法字符 8 if PATINDEX('%[^0123456789]%', @Number)>0 9 begin 10 return @Number 11 end 12 --如果长度小于4位,eg:2004,200,就直接返回 13 else if len(@Number)<=4 14 begin 15 return @Number 16 end 17 --如果长度为5位,eg:20045,返回2004-5 18 else if len(@Number)=5 19 begin 20 return left(@Number,4)+'-'+right(@Number,1) 21 end 22 --如果长度为6位 23 else if len(@Number)=6 24 begin 25 if right(@Number,2)<='12' 26 begin 27 return left(@Number,4)+'-'+right(@Number,2) 28 end 29 else 30 begin 31 return left(@Number,4)+'-'+left(right(@Number,2),1)+'-'+right(@Number,1) 32 end 33 end 34 --如果长度为7位 35 else if len(@Number)=7 36 begin 37 --第5,6位组合起来,小于12 eg:2015111 2015-11-1 38 if left(right(@Number,3),2)<='12' 39 begin 40 return left(@Number,4)+'-'+left(right(@Number,3),2)+'-'+right(@Number,1) 41 end 42 --第5,6位组合起来大于12,且第6,7位小于31, eg:2015211 2015-2-11 43 else if left(right(@Number,3),2)>'12' and left(@Number,2)<='31' 44 begin 45 return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,2) 46 end 47 --第5,6位组合起来大于12,且第6,7位大于31, eg:2015235 不合理 48 else 49 return @Number 50 end 51 --如果长度为8位 52 else if len(@Number)=8 53 begin 54 if left(right(@Number,4),2)<='12' and right(@Number,2)<='31' 55 begin 56 return left(@Number,4)+'-'+left(right(@Number,4),2)+'-'+right(@Number,2) 57 end 58 else --不合法 59 begin 60 return @Number 61 end 62 end 63 else --不合法 64 begin 65 return @Number 66 end 67 return '0' 68 end