CODEWITHSUNDEEP
sqlregexoracle-database
Lorik Berisha
Lorik Berisha

Reputation: 263

Oracle regexp to validate time

I am trying to validate a regular expression in oracle SQL to check Time format:

The desired format I want to check is:

2/27/2020 3:53:02 PM

I have already created a regexp for a date format such as:

20200227 --> using REGEXP_LIKE('20190222', '^\d{4}(0[1-9]|(1[0-2]))(0[1-9]|[1-2][0-9]|3[0-1])$')

Could someone give me a hint for the time format?

Upvotes: 0

Views: 2673

Answers (3)

Lorik Berisha
Lorik Berisha

Reputation: 263

While looking into string Regex, I found that this one does the job that I'm looking for:

REGEXP_LIKE('2/27/2020 3:53:02 PM','^\d{1,2}\/\d{1,2}\/\d{4} \d{1,2}:\d{1,2}:\d{1,2} [AP]M\z')

Upvotes: 0

MT0
MT0

Reputation: 167932

Don't use a regular expression as the edge cases will make the expression long and complicated (i.e. months can have 28-31 days, leap years every 4 years ... except for multiples of 100 ... except for multiples of 400).

For example, your date regular expression ^\d{4}(0[1-9]|(1[0-2]))(0[1-9]|[1-2][0-9]|3[0-1])$ would validate 20190229 or 20200230 or 20200931 neither of which are valid dates.

A time regular expression is easier as you only have hours 0-23, minutes 0-59, seconds 0-59 (assuming you are ignoring leap seconds) and possibly fractional seconds and would be:

([01]\d|2[0-3]):[0-5]\d:[0-5]\d(\.\d+)?

However, a simpler way is just to try to perform a conversion to a DATE and if it fails then you know it isn't valid. If you are on Oracle 12.2 or later then you can use the built-in VALIDATE_CONVERSION function:

SELECT validate_conversion( '20200230000000' AS DATE, 'YYYYMMDDHH24MISS' )
FROM   DUAL

If you are on an earlier version then you can create a custom function to try and perform the conversion and if an exception occurs then you know the input is invalid:

CREATE FUNCTION isValidDate(
  date_string  IN VARCHAR2,
  format_model IN VARCHAR2 DEFAULT 'FXYYYYMMDDHH24MISS'
) RETURN NUMBER
IS
  d DATE;
BEGIN
  d := TO_DATE( date_string, format_model );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

To me, it looks as if you started it wrong. If you want to validate date (time) format, you're storing it as a string, which is a big mistake.

If you set that column (or whatever it is) as DATE, which - in Oracle - contains both date and time, then database will take care that you can enter only valid values.

Format you mentioned, or any other, is matter of display, not storage.

One option is to force TO_DATE conversion; something like this:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> set ver off
SQL> select to_date('&date_value', 'mm/dd/yyyy hh:mi:ss am') result from dual;
Enter value for date_value: 2/27/2020 3:53:02 pm

RESULT
-------------------
27.02.2020 15:53:02

SQL> /
Enter value for date_value: 13/54/2020 x:23:83 am
select to_date('13/54/2020 x:23:83 am', 'mm/dd/yyyy hh:mi:ss am') result from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

So: if conversion works, then format is OK. Otherwise, you entered something stupid and conversion won't pass. Such a code can be used as a function which returns e.g. Boolean.

Upvotes: 3

Related Questions