Check valid date on oracle

Asked

Viewed 823 times

-2

I need a help, I’m trying to bring the valid dates of the bank because there are dates with invalid formats in it, below follows the code I’m using, can someone give me a hand? I’ve done it all...

SELECT b.mec_dtmovi,
   CASE                   
     WHEN regexp_like(b.mec_dtmovi,'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)') THEN
       to_date(b.mec_dtmovi,'dd/mm/yyyy') 
      ELSE 
        null END

     FROM VIS_MECDIG b

The result is like this with the right side blank, it always goes to Else:

MEC_DTMOVI CASEWHENREGEXP_LIKE(B.MEC_DTMO 17/03/2014
17/03/2014
20/03/2014
27/03/2014
20/03/2014
20/03/2014
27/03/2014
28/03/2014
28/03/2014
27/03/2014
27/03/2014
27/03/2014
27/03/2014
27/03/2014
27/03/2014
27/03/2014
17/03/2014
28/03/2014
28/03/2014
27/03/2014

I did the function shown below:

create or replace function valida_data(v_data varchar2) return varchar2 is

v_data1 date;

begin 

select to_date(v_data) into v_data1 from dual;
return v_data;
exception
when value_error then
return null;  
end;

But I’m having problems using select with Where, when I use some function of oracle to add or subtract days with sysdate or not and etc, as I don’t know much of oracle researched enough but I didn’t find anything that solved my problem, below is the select I used, and I registered for purpose in the date field which is of the type varchar in the table invalid characters like point, question and etc... To see if the function would bring the date anyway, because in the scenario I need to bring the valid dates and those that are not valid will have to stay null or show some message in the field with the date that has the invalid character or wrong format. Remember that this is difficult because the field is varchar2 instead of date a bank modeling error but the company does not want to change either the bank nor the front nor the back end thus allowing the user by invalid characters in the field...

SELECT * FROM TB_TESTE_DATA_01 WHERE teste_data(data_admissao) = trunc(sysdate);

Someone can help this poor man here?

oracle oracle11g oracle12c oracle11c
  • 1

    Do not use regex to validate dates, read here to understand why this is not a good idea. In Oracle I think it is easier to use the date functions themselves, for example: https://stackoverflow.com/a/14702163

  • @hkotsubo, edited my question and added the function I had quoted, but was unsuccessful.

1 answer

0


I managed to solve after hours testing, but God is good all the time and allowed me to find the solution, below follows the solution:

SELECT * FROM  TB_TESTE_DATA_01 b WHERE  b.DATA_ADMISSAO = 
(CASE 
WHEN regexp_like(DATA_ADMISSAO,'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.] 
(19|20)') THEN 
    b.DATA_ADMISSAO
      ELSE 
       NULL 
   END) AND b.data_admissao <= TRUNC(SYSDATE);

Or so:

SELECT b.data_admissao, CASE WHEN regexp_like(DATA_ADMISSAO,'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)') THEN 
    b.DATA_ADMISSAO   
      ELSE 
       'data errada'
   END FROM  TB_TESTE_DATA_01 b WHERE  b.DATA_ADMISSAO = 
(CASE 
WHEN regexp_like(DATA_ADMISSAO,'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012]) 
[- /.](19|20)') THEN 
    b.DATA_ADMISSAO   
      ELSE 
       'data errada'
   END)

It got a little rough but worked perfectly, in case it error using Sqlplus remove the 'wrong date' from ELSE and put NULL. If anyone has a better way to do this please show me, I’m new with Oracle and SQL... Remember that this is difficult because the field is varchar2 instead of date a bank modeling error but the company does not want to change either the bank nor the front nor the back end thus allowing the user by invalid characters in the field...

Browser other questions tagged oracle oracle11g oracle12c oracle11c

You are not signed in. Login or sign up in order to post.