Ug. Why can't people store dates as in the correct data type and avoid all these issues?!
Anyway. To test the values are dates between two values, you need to to_date them. But first you need to exclude the invalid date.
If you don't want to build your own isdate function, you could use regular expressions to validate the string. For example, if you know dates will be in DD-MON-YYYY format:
set define off
create table t (
x varchar2(100)
);
insert into t values ('1226547879');
insert into t values ('basdhbcus489');
insert into t values ('34^&dsulch56');
insert into t values ('01-JAN-2010');
insert into t values ('01-DEC-1929');
insert into t values ('01-MAY-2031');
with rws as (
select to_date(x, 'DD-MON-YYYY') x_dt from t
where regexp_like(x, '[0-9]{2}-(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)-[0-9]{4}')
)
select * from rws
where x_dt between date'1930-01-01' and date'2030-01-01';
X_DT
01-JAN-2010 00:00:00
If you don't know which format the dates will be in, or expect multiple different formats, you need to test them all. Then have a case expression to call to_date with the correct format mask. It's often easier to build your own function to do this in one step, catching the invalid conversion exceptions.
Fortunately this gets much easier in 12.2! You could use validate_conversion to find the rows that convert to a given mask:
with rws as (
select to_date(x, 'DD-MON-YYYY') x_dt from t
where validate_conversion(x as date, 'DD-MON-YYYY') = 1
)
select * from rws
where x_dt between date'1930-01-01' and date'2030-01-01';
X_DT
01-JAN-2010 00:00:00
Or the "default on conversion error" clause of to_date to return a "magic date":
with rws as (
select to_date(x default '01-JAN-0001' on conversion error, 'DD-MON-YYYY') x_dt from t
)
select * from rws
where x_dt between date'1930-01-01' and date'2030-01-01';
X_DT
01-JAN-2010 00:00:00