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

SQL Server判断是否满足日期格式(YYYYMMDD)以及中文等判断,格式化为YYYY-MM-DD,第1张

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

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/586542.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-04-12
下一篇 2022-04-12

发表评论

登录后才能评论

评论列表(0条)

保存