Test date field in Oracle query

Question

need to ask a question, I am using this to_date shown below, for now we have no validation and sometimes the user puts the character (...) or other invalid character in the field date, breaking thus to_date, please as I can only pick up the date and when it is invalid character it should show on the line a blank die or a predefined fixed date, as if it were an if because the line with the invalid date will have to come too.

TO_DATE(PESQUISA_MEDALHA.PREVISAO_ENVIO, 'DD/MM/YY') = trunc(SYSDATE)

Answer 1

You can use a regular expression to validate if the typed text matches what you expect.

The function regexp_like checks whether the reported value matches the expected standard. See how it looks:

WITH datas AS (
SELECT '13/10/2000' DATA FROM dual  UNION ALL
SELECT '01/01/2020' DATA FROM dual  UNION ALL
SELECT '13 10/2000' DATA FROM dual  UNION ALL
SELECT '13/13/2000' DATA FROM dual  UNION ALL
SELECT '13/13/2100' DATA FROM dual  UNION ALL
SELECT '13/13/1800' DATA FROM dual  UNION ALL
SELECT '32/11/1800' DATA FROM dual )

SELECT DATA,
       CASE 
         WHEN Regexp_like(DATA,'(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)') THEN 
           to_date(DATA,'dd/mm/yy') 
          ELSE 
           NULL 
       END sua_data
  FROM datas 

See this example running SQL Fiddle.

But as commented above, the ideal would be to record the data correctly in the database.