检查字符串是否为有效日期

Question

我有一个表，其中包含5条记录的字段sourcefilename。以下是记录。

SN. SOURCEFILENAME
1. 20170215095453_1.Iredell Memorial Hospital Dental Eligibility.xls_INFREPT01.txt
2. Iredell Memorial Hospital Eligibility April 2017.xls_INFREPT01.txt
3. Iredell Memorial Hospital Eligibility March 2017.xls_INFREPT01.txt
4. Iredell Memorial Hospital Eligibility May 2017.xls_INFREPT01.txt
5. Iredell Memorial Hospital October 2016 Dental Eligibility.xls_EligData.txt

我只需要提取前8个字符，并检查它是一个有效的日期。如果是有效日期，则返回TRUE，否则返回FALSE。

我试过ISDATE函数。还有没有别的选择？

SELECT DISTINCT SubStr(sourcefilename,1,8),
CASE WHEN isdate(SubStr(sourcefilename,1,8),'YYYYMMDD') = 1 THEN 'TRUE' ELSE 'FALSE' END  FROM ai_4451_1_metl;

Answer 1

Oracle12.2提供了一个新的函数VALIDATE_CONVERSION。到目前为止，我从未使用过它，但我假设它会是这样的：

CASE VALIDATE_CONVERSION(SubStr(sourcefilename,1,8) AS DATE, 'YYYYMMDD') 
   WHEN 1 THEN 'TRUE'
   ELSE 'FALSE'
END

Answer 2

如果您有Oracle12.2，则可以使用validate_conversion函数：

with demo as
     ( select '20170101' as sourcetest from dual union all
       select '20171100' from dual )
select sourcetest
     , validate_conversion(sourcetest as date, 'YYYYMMDD') as test_result
from   demo;

SOURCETEST  TEST_RESULT
----------- -----------
20170101              1
20171100              0

Answer 3

没有Oracle built_in isdate()或不同名称的等价物(在12cR2之前的版本中)。但您可以编写自己的代码：

create or replace function isdate
   ( p_date_str in varchar2 
     , p_date_fmt in varchar2 )
   return varchar2
is
    return_value varchar2(5);
    l_date date;
begin
    begin
        l_date := to_date(p_date_str, p_date_fmt);
        return_value := 'TRUE';
    exception
        when others then
            return_value := 'FALSE';
    end;
    return return_value;
end isdate;
/

如果将字符串转换为日期数据类型成功，则为有效日期，否则为无效日期。

如果您的数据质量问题导致字符串具有多种日期格式，那么您可以实现此替代解决方案。

create or replace function isdate
   ( p_date_str in varchar2  )
   return varchar2
is
    return_value varchar2(5) := 'FALSE';
    l_date date;
    l_date_fmts sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll (
         'DD-MON-YYYY'
         , 'YYYY-MM-DD'
         , 'DD-MM-YY'
            -- etc
         );

begin
    for idx in 1..l_date_fmts.count() loop
        begin
            l_date := to_date(p_date_str, l_date_fmts)idx) );
            return_value := 'TRUE';
            exit;
        exception
            when others then
                null;
        end;
    end loop;
    return return_value;
end isdate;
/