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


    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
  • 相关阅读:
    优化后的组合算法
    Android之——AsyncTask和Handler对照
    LeetCode(30) Substring with Concatenation of All Words
    HDU--1054--Strategic Game【最小点覆盖】
    最小生成树模板(poj3625)
    QT中使用高速排序
    走进APICloud的世界 (1)
    layer,一个可以让你想到即可做到的javascript弹窗(层)解决方案
    解决Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future:
    移动端H5的一些基本知识点总结
  • 原文地址:https://www.cnblogs.com/dz-boss/p/7678737.html
Copyright © 2020-2023  润新知