Valid Date Checks in Oracle

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.

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;

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;

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.

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;

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