SQL Server判断是否满足日期格式（YYYYMMDD）以及中文等判断，格式化为YYYY-MM-DD

SQL Server判断是否满足日期格式（YYYYMMDD）以及中文等判断，格式化为YYYY-MM-DD

SQL Server判断是否满足日期格式（YYYYMMDD）以及中文等判断：

在做sql数据的正确性审核中，需要判断数据是否满足日期格式，网上找不到相关的资料，于是自己花了半天写了一个简单的函数

具体思路：

1.判断字符串是否8位

2.如果是8位，通过datalength判断是否8位，如果不是8位，说明字符串中存在汉字或者不可见字符

3.通过Ascii码对8位中的每一位字符进行判断，目的是判断是否数字（按照AsciI码可以避免不可见字符）

4.如果符合上述3中条件，说明字符是8位的数字，判断是否正常日期

ALTER FUNCTION [dbo].[isDataYYYYMMDD]( @Number nvarchar(50))

RETURNS int

AS

begin

　　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

　　　　begin

　　　　　　return 1

　　　　end

　　else if datalength(ltrim(rtrim(@Number)))<> 8

　　　　if ascii(left(@Number,1))> 57 or ascii(left(@Number,1))<48

　　　　　　or ascii(right(left(@Number,2),1))> 57 or ascii(right(left(@Number,2),1))<48

　　　　　　or ascii(right(left(@Number,3),1))> 57 or ascii(right(left(@Number,3),1))<48

　　　　　　or ascii(right(left(@Number,4),1))> 57 or ascii(right(left(@Number,4),1))<48

　　　　　　or ascii(right(left(@Number,5),1))> 57 or ascii(right(left(@Number,5),1))<48

　　　　　　or ascii(right(left(@Number,6),1))> 57 or ascii(right(left(@Number,6),1))<48

　　　　　　or ascii(right(left(@Number,7),1))> 57 or ascii(right(left(@Number,7),1))<48

　　　　　　or ascii(right(left(@Number,8),1))> 57 or ascii(right(left(@Number,8),1))<48

　　　　　　begin

　　　　　　　　return 1

　　　　　　end

　　　　else

　　　　　　begin

　　　　　　　　return 0

　　　　　　end

　　else

　　　　begin

　　　　　　if DATENAME ( year ,@Number)>2017

　　　　　　　　begin

　　　　　　　　　　return 1

　　　　　　　　end

　　　　　　else if DATENAME ( month ,@Number)>12 or DATENAME ( month ,@Number)<1

　　　　　　　　begin

　　　　　　　　　　return 1

　　　　　　　　end

　　　　　　else if DATENAME ( DAY ,@Number)>31 or DATENAME ( DAY ,@Number)<1

　　　　　　　　begin

　　　　　　　　　　return 1

　　　　　　　　end

　　　　end

　　return 0

end

2.格式化为YYYY-MM-DD

去掉0:20170909:2017-9-9

ALTER FUNCTION [dbo].[formatTime]( @Number nvarchar(500))

RETURNS nvarchar(500)

--返回-1，表示字符串存在问题

--else返回其计算结果

AS

begin

--判断非法字符

if PATINDEX('%[^0123456789]%', @Number)>0

begin

return @Number

end

--如果长度小于4位，eg：2004，200，就直接返回

else if len(@Number)<=4

begin

return @Number

end

--如果长度为5位，eg：20045，返回2004-5

else if len(@Number)=5

begin

if right(@Number,1)='0'

begin

return left(@Number,4)

end

else

begin

return left(@Number,4)+'-'+right(@Number,1)

end

end

--如果长度为6位

else if len(@Number)=6

begin

if right(@Number,2)<='12'

begin

if left(right(@Number,2),1)='0'

begin

return left(@Number,4)+'-'+right(@Number,1)

end

else

begin

return left(@Number,4)+'-'+right(@Number,2)

end

end

else

begin

if right(@Number,1)='0'

begin

return left(@Number,4)+'-'+left(right(@Number,2),1)

end

else

begin

return left(@Number,4)+'-'+left(right(@Number,2),1)+'-'+right(@Number,1)

end

end

end

--如果长度为7位

else if len(@Number)=7

begin

--第5,6位组合起来，小于12 eg:2015111 2015-11-1

if left(right(@Number,3),2)<='12'

begin

if left(right(@Number,3),1)='0' and right(@Number,1)<>'0'

begin

return left(@Number,4)+'-'+left(right(@Number,2),1)+'-'+right(@Number,1)

end

else if left(right(@Number,3),1)='0' and right(@Number,1)='0'

begin

return left(@Number,4)+'-'+left(right(@Number,2),1)

end

else if left(right(@Number,3),1)<>'0' and right(@Number,1)='0'

begin

return left(@Number,4)+'-'+left(right(@Number,3),2)

end

else

begin

return left(@Number,4)+'-'+left(right(@Number,3),2)+'-'+right(@Number,1)

end

end

--第5,6位组合起来大于12，且第6,7位小于31， eg:2015211 2015-2-11

else if left(right(@Number,3),2)>'12' and right(@Number,2)<='31'

begin

if right(@Number,2)='00'

begin

return left(@Number,4)+'-'+left(right(@Number,3),1)

end

else if left(right(@Number,2),1)='0'

begin

return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,1)

end

end

--第5,6位组合起来大于12，且第6,7位大于31， eg:2015235 不合理

else

return @Number

end

--如果长度为8位

else if len(@Number)=8

begin

if left(right(@Number,4),2)<='12' and right(@Number,2)<='31'

begin

if left(right(@Number,4),2)='00' or right(@Number,2)='00'

begin

return @Number

end

else if left(right(@Number,4),1)='0' and left(right(@Number,2),1)='0'

begin

return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,1)

end

else if left(right(@Number,4),1)='0' and left(right(@Number,2),1)<>'0'

begin

return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,2)

end

else if left(right(@Number,4),1)<>'0' and left(right(@Number,2),1)='0'

begin

return left(@Number,4)+'-'+left(right(@Number,4),2)+'-'+right(@Number,1)

end

else

return left(@Number,4)+'-'+left(right(@Number,4),2)+'-'+right(@Number,2)

end

else --不合法

begin

return @Number

end

end

else --不合法

begin

return @Number

end

return '0'

end

不去0:20170909:2017-09-09

ALTER FUNCTION [dbo].[formatTime1]( @Number nvarchar(500))

RETURNS nvarchar(500)

--返回-1，表示字符串存在问题

--else返回其计算结果

AS

begin

--判断非法字符

if PATINDEX('%[^0123456789]%', @Number)>0

begin

return @Number

end

--如果长度小于4位，eg：2004，200，就直接返回

else if len(@Number)<=4

begin

return @Number

end

--如果长度为5位，eg：20045，返回2004-5

else if len(@Number)=5

begin

return left(@Number,4)+'-'+right(@Number,1)

end

--如果长度为6位

else if len(@Number)=6

begin

if right(@Number,2)<='12'

begin

return left(@Number,4)+'-'+right(@Number,2)

end

else

begin

return left(@Number,4)+'-'+left(right(@Number,2),1)+'-'+right(@Number,1)

end

end

--如果长度为7位

else if len(@Number)=7

begin

--第5,6位组合起来，小于12 eg:2015111 2015-11-1

if left(right(@Number,3),2)<='12'

begin

return left(@Number,4)+'-'+left(right(@Number,3),2)+'-'+right(@Number,1)

end

--第5,6位组合起来大于12，且第6,7位小于31， eg:2015211 2015-2-11

else if left(right(@Number,3),2)>'12' and left(@Number,2)<='31'

begin

return left(@Number,4)+'-'+left(right(@Number,3),1)+'-'+right(@Number,2)

end

--第5,6位组合起来大于12，且第6,7位大于31， eg:2015235 不合理

else

return @Number

end

--如果长度为8位

else if len(@Number)=8

begin

if left(right(@Number,4),2)<='12' and right(@Number,2)<='31'

begin

return left(@Number,4)+'-'+left(right(@Number,4),2)+'-'+right(@Number,2)

end

else --不合法

begin

return @Number

end

end

else --不合法

begin

return @Number

end

return '0'

end