Now-a-days it becomes common practice for newbies to use varchar datatype to store dates. My first advice is to use proper DATETIME datatype. Sometimes it is preferred to use varchar when importing dates from other systems(to avoid any errors if the source dates are in different format). In that case it is required to see if values are actually dates. To check if a value is proper date, it is suggested to use ISDATE()
eg
select isdate('2007-10-12'),isdate('12-12-2006'),isdate('March 12, 2007')
Now it should be noted that the following return 1
select isdate(2007),isdate('2007')
becuase the values in a ISDATE functions are first casted to datetime
select cast(2007 as datetime)
------------------------------------------------------
1905-07-01 00:00:00.000
select cast('2007' as datetime)
------------------------------------------------------
2007-01-01 00:00:00.000
Which are valid dates
So if varchar datatype is used to stored formatted dates (ex YYYYMMDD), to check if it is valid date, you need not only to use ISDATE() function, but also use LEN() function
Consider the following set of data and see the difference
declare @dates table (dates varchar(8))
insert into @dates(dates)
Select '20071201' union all
Select '20071111' union all
Select '2007' union all
Select 2007 union all
Select '1800'
select dates from @dates
where ISDATE(dates) =1
--Result
dates
--------
20071201
20071111
2007
2007
1800
--Result
select dates from @dates
where ISDATE(dates) =1 and LEN(dates)=8
dates
--------
20071201
20071111