CODEWITHSUNDEEP
oracle-databaseplsql
Sujan Shrestha
Sujan Shrestha

Reputation: 1040

Check String of characters is a valid date or not

I have a table with field sourcefilename which has 5 records. Following are the records.

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

I just need to extract first 8 characters and check if its a valid date. If it is a valid date then return TRUE else FALSE.

I tried ISDATE function. Is there are any other alternatives?

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;

Upvotes: 3

Views: 9090

Answers (3)

APC
APC

Reputation: 146269

There is no Oracle built_in isdate() or differently named equivalent (in versions before 12cR2). But you can write your own:

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;
/

If casting the string to a DATE datatype succeeds it's a valid date, otherwise it isn't.

If your data quality issues are such that your strings have multiple date formats then you can implement this alternate solution. 

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;
/

Upvotes: 3

William Robertson
William Robertson

Reputation: 16001

If you have Oracle 12.2 you can use the validate_conversion function:

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

Upvotes: 4

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

Oracle 12.2 provides a new function VALIDATE_CONVERSION. So far I never used it, but I assume it would be like this:

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

Upvotes: 7

Related Questions