Checking a specific date format in Oracle SQL using RegexLike

Question

I am using oracle data integrator as an ETL tool to load data, there is one column which as a source comes in format YYYYMMDD for ex: 20190418. I want to query that table to identify all those records which do not fit 20190418 or YYYYMMDD format. P.S. the column is in varchar2 datatype.

I already tried using something like this:

SELECT  CASE WHEN NOT REGEXP_LIKE('20190418', '^\d{4}(0[1-9]|1[12])(0[1-9]|[12]\d|3[01])$') then '00000000' else '20190418' END FROM DUAL;

This seems to identify those illegal dates, but for example it didn't work for this one : '20181023'.

Can someone figure it out, I think I am missing some kind of pattern

Wernfried Domscheit · Accepted Answer · 2019-04-18 06:56:34Z

2

I would propose a function rather than a regex, will be easier.

CREATE OR REPLACE FUNCTION Verify_date_number(inNumber IN NUMBER) RETURN VARCHAR2 AS        
    res DATE;
BEGIN   
    res := TO_DATE(inNumber, 'fxYYYYMMDD');       
    RETURN TO_CHAR(res, 'YYYYMMDD');       
EXCEPTION    
    WHEN OTHERS THEN        
        RETURN '00000000';
END;

If you are running Oracle 12.2 then you can also use VALIDATE_CONVERSION

SELECT
   CASE VALIDATE_CONVERSION('20190418' AS DATE, 'fxYYYYMMDD')
   WHEN 1 THEN '20190418'
   ELSE '00000000'
   END
FROM dual;

answered Apr 18, 2019 at 6:56

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kaushik Nayak Over a year ago

TO_DATE with DEFAULT .. ON CONVERSION ERROR may be better?

Wernfried Domscheit Over a year ago

TO want to get a number like 20190418 rather than a DATE value. You cannot return a DATE for 00000000

Barbaros Özhan · Accepted Answer · 2019-04-18 07:03:54Z

1

Using PL/SQL maybe a better alternative

declare
   v_date     date;
begin
  for c in
  (
   select '20190418' as date_str from dual union all
   select '20191804' from dual union all
   select '201904187' from dual
  )
  loop
    begin    
       v_date := to_date(c.date_str,'yyyymmdd');    
       dbms_output.put_line(c.date_str);
      exception when others then null;   
    end;    
  end loop;  
end;

gives only the decently formatted data as output. In the above case, it's 20190418 only.

For 20191804, we would get ORA-01843: not a valid month

For 201904187, we would get ORA-01830: date format picture ends before converting entire input string

errors

edited Apr 18, 2019 at 7:03

answered Apr 18, 2019 at 6:57

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

hotfix · Accepted Answer · 2019-04-18 06:53:41Z

0

you can try this one:

with tab as(
  select '20190418' as dat from dual union all
  select '20181023' as dat from dual union all  
  select '20181123' as dat from dual union all  
  select '20181223' as dat from dual union all  
  select '20181201' as dat from dual union all  
  select '20181209' as dat from dual union all  
  select '20181210' as dat from dual union all  
  select '20181229' as dat from dual union all  
  select '20181231' as dat from dual union all  
  select '20181232' as dat from dual union all    
  select '20181200' as dat from dual union all  
  select '20191418' as dat from dual 
)
SELECT  CASE WHEN NOT REGEXP_LIKE(dat, '^\d{4}(0[1-9]|(1[0-2]))(0[1-9]|[1-2][0-9]|3[0-1])$') then '00000000' else dat END  as dat

FROM tab;

Result:

answered Apr 18, 2019 at 6:53

hotfix

3,39624 silver badges36 bronze badges

1 Comment

Wernfried Domscheit Over a year ago

This will not work for values like 20190231 - the 31st of February does not exist.

Collectives™ on Stack Overflow

Checking a specific date format in Oracle SQL using RegexLike

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related