日常数据库中包含日期的字段，但是是用varchar存储的，导致格式不统一的

在使用数据格式化成date时是很有用的一个函数。

StringDate

CREATE DEFINER=`root`@`%` FUNCTION `StringDate`(  str varchar(100) ) RETURNS date
    DETERMINISTIC
BEGIN

     DECLARE str2 VARCHAR(100);
     DECLARE str3 VARCHAR(100);
     
     set str2 = StringNULL(str);
     if str2 is null then 
            return null;
     end if;
     
     set str2 = REPLACE(str2,'/','-');
     set str2 = REPLACE(str2,'.','-');
     set str2 = REPLACE(str2,'——','-');
     set str2 = REPLACE(str2,'—','-');
     set str2 = REPLACE(str2,'*','-');
     set str2 = REPLACE(str2,'、','-');
     set str2 = REPLACE(str2,'\\','-');
     set str2 = REPLACE(str2,',','-');
     set str2 = REPLACE(str2,'。','-');
     set str2 = REPLACE(str2,'_','-');
     
     if LENGTH(str2) = 10 then 
          set str3 = SUBSTRING_INDEX(str2,'-',1);
         if cast(str3 as   SIGNED   INTEGER) > 1700 then 
            return STR_TO_DATE(str2,'%Y-%m-%d');
         else
            return STR_TO_DATE(str2,'%d-%m-%Y');
         end if;
         elseif LENGTH(str2) = 8 then 
          set str3 = SUBSTRING(str2,0,4);
         if cast(str3 as   SIGNED   INTEGER) > 1700 then 
            return STR_TO_DATE(str2,'%Y%m-%d');
         else
            return STR_TO_DATE(str2,'%d%m%Y');
         end if;
     end if;
     
END

Mysql 函数字符串转日期_字段

image.png