Valid Date Checks in Oracle

Question

I have a date value (either valid date or invalid date) store in varchar format. Is it possible to check the date is valid or not in sql query.

Romanesque

Answer 1

Yes, if you know the format and with little plsql.

Let's say you have the date in format 'yyyy-mon-dd hh24:mi:ss'.

create function test_date(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'yyyy-mon-dd hh24:mi:ss') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;

Now you can:

select your_date_col, test_date(your_date_col)
from your_table;

Flavoprotein

Is it possible to perform the check without pl/sql – Romanesque

you cannot create the function? – Flavoprotein

I'm using unix shell files for data loading and I'm not sure that I can have functions or not – Romanesque

If you are using sqlldr to load files, then 1. the rows that are invalid can/will be rejected into a .bad file. 2. Yes you can use a function in a ctl. – Flavoprotein

Thanks Florin Will try it. I'm using a control file and will follow the approach #2. – Romanesque

Answer 2

You can do it in a block like

BEGIN
  select TO_DATE(your_date,'YYYYMMDD') from dual; --ADD INTO V_DUMMY IN PLSQL
EXCEPTION WHEN OTHERS THEN dbms_output.put_line('NOT A VALID DATE');
END;

Bottoms

Answer 3

Without using block or creating custom functions or procedures, you can use just the select statement below so it returns the true date value parsed from the date string and returns null if the date string cannot be recognized.

This approach has limitation. Please see notes below.

select
  case
    when regexp_substr(DateStr,'^[[:digit:]]{2}-[[:digit:]]{2}-[[:digit:]]{4}$') is not null then
      case
        when to_number(regexp_substr(DateStr,'[^-]+',1,1))<=12 and to_number(regexp_substr(DateStr,'[^-]+',1,2))<=31
        then add_months(to_date('01-01-1900','MM-DD-YYYY'),(to_number(regexp_substr(DateStr,'[^-]+',1,3))-1900)*12+to_number(regexp_substr(DateStr,'[^-]+',1,1))-1)+to_number(regexp_substr(DateStr,'[^-]+',1,2))-1
        else null
      end
    else null
  end RealDateVal
from MyTable

This example takes date format as "MM-DD-YYYY", and assumes "MyTable" to have the date string column "DateStr". You may have to adjust accordingly for you need.

The overall approach is to first check the date string to be in the format ??-??-???? where a "?" is a digit, and then check the first ?? to be no larger than 12 (for the month) and the second ?? to be no larger than 31 (for the date). If all pass, then to assembly a date-typed value by using the "add_months()" function to build the year and month, and using the "+" function to build the date. If any of these criteria is not satisfied, then to return null.

Note 1: As we have no simple way to check if a year has Feb-29th and I am lazy in checking if a month has the 31st, a day presented as an invalid 29th, 30th or 31st, that would not be caught by the validity check, will be pushed to the next month. (See examples below.) And the query will never return an error.

Note 2: "case when ... is not null then ... else ... end" is lazy, so it won't evaluate the "then ..." part to potentially trigger error, in the case if "when ... is not null" is not satisfied. This feature is fundamental to the overall approach. Instead, the function nvl2() is not lazy and cannot be used instead.

Examples:
'06-15-2015' -> returns valid date
'06-15-2015 ' -> returns null
'06/15/2015' -> returns null
'15-06-2015' -> returns null
'06-31-2015' -> return valid date as 07-01-2015
'02-30-2015' -> return valid date as 03-02-2015

Go further: Depending on how far you want to go, you can mitigate the limitation exhibited in "Note 1" by converting the parsed date value back into string and compare it with the original date string value.

Afterpiece

Actually there is a simple way to check if Feb-29th.If year % 4 == 0 then Feb-29th is true. – Examinant

Answer 4

Just use to_date function to check wether the date is valid/invalid.

BEGIN
  select TO_DATE(your_date,DateFormat) from dual;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line('NOT A VALID DATE');
END;

Fingerprint

Answer 5

And this approach is also a simple select statement without using blocks, custom functions or stored procedures.

This example assumes the date format to be "MM-DD-YYYY". It first validates the format being "??-??-????" where "?" is a digit by using regular expression, and then validates the month (the first "??") being not larger than 12, and then finds the last day of that given valid year/month with the last_day() function to validate if the day part (the second "??") to be a valid one for that month. When all are valid, then uses the to_date() function to parse, otherwise returns null.

select
  case
    when regexp_substr(DateStr,'^[[:digit:]]{1,2}-[[:digit:]]{1,2}-[[:digit:]]{4}$') is not null
    then case
      when to_number(regexp_substr(DateStr,'[^-]+',1,1))<=12
      then case
        when to_number(regexp_substr(DateStr,'[^-]+',1,2)) <= extract(day from last_day(add_months(to_date('01-01-1900','MM-DD-YYYY'),(to_number(regexp_substr(DateStr,'[^-]+',1,3))-1900)*12+to_number(regexp_substr(DateStr,'[^-]+',1,1))-1)))
        then to_date(DateStr,'MM-DD-YYYY')
        else null
        end
      else null
      end
    else null
    end InstallDate
from MyTable

Afterpiece

Answer 6

FUNCTION IsDate( p_str IN VARCHAR2, p_format IN VARCHAR2 ) RETURN NUMBER AS
V_date DATE;
BEGIN
    V_Date := TO_DATE( p_str, p_format );
    RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

call:

select lastlogin, IsDate(lastlogin,'MM/dd/yyyy  hh24:mi:ss') from users_table

Pleiad

Answer 7

Maybe too late, but I just found this elegant solution in the oracle 19 documentation:

select cast('2024-01-07' as DATE DEFAULT '9999-12-31' ON CONVERSION ERROR, 'YYYY-MM-DD')
as mydate from dual;

Ubald