Test date field in Oracle query

Asked

Viewed 370 times

-2

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)

  • could not treat this error in the application not to let the user inform invalid date? I think better than do treatment in this condition (I believe you are performing a select)

1 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.

  • Thank you so much for the kindness and attention in answering my noble, I will test and then put as settled, all the good in your life!!!

  • Bruno is possible to explain me how you made this REGEXP_LIKE validation within the function, I am new with sql...

  • REGEXP_LIKE validates if its "value" matches the character identification pattern. In the case of the pattern, quite simply it is something like NN/NN/NNNN (where N represents a number). More information at the link: https://pt.wikipedia.org/wiki/Express%C3%A3o_regular.

  • 1

    Read and understood thanks for sharing, thanks anyway!!!

  • can help me again with your wise knowledge, I need this same regular expression but now I need it coming beyond the dates minutes and seconds hours but I could not solve alone unfortunately...

Browser other questions tagged

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