How to Filter using SQL IsDate() Function and Cast

In your career you may run into an issue where you have a date stored as a varchar.

When this happens, you may need to use the SQL IsDate in the Where Clause.

You need to first use the SQL IsDate Function to check if the value is a valid date.

Then you can convert the varchar to a date using Cast or Convert.

Even though you have filtered on the SQL Isdate, you might still have a problem with the conversion.

This video shows you how to deal with it.

I don’t mention it in the video, but when dealing with dates, you always have to keep in mind the default date settings of the database.

Cast and Isdate use these defaults and could cause you some trouble if you aren’t aware of them.

Here are the scripts to run the queries that are in the video.

create table emp
(
id int primary key identity(1,1)
,emp_name varchar(50)
,emp_birthday varchar(20)
)
GO
insert into emp (emp_name,emp_birthday) values ('Bob','12/12/2001');
insert into emp (emp_name,emp_birthday) values ('Mary','123');
insert into emp (emp_name,emp_birthday) values ('Jill','12/1/2005');
insert into emp (emp_name,emp_birthday) values ('Jim','1/12/1999');
insert into emp (emp_name,emp_birthday) values ('Sue','7/15/2011');
insert into emp (emp_name,emp_birthday) values ('Sally','20120315');
GO

select id,emp_name,emp_birthday
,isdate(emp_birthday)
from emp
where isdate(emp_birthday)=1
and 
case when isdate(emp_birthday)=0 then cast(null as date)
    else cast(emp_birthday as date)
end < '1/1/2006'

Here is the YouTube Video SQL Isdate Function as a Filter with Cast .

Tags: SQL, SQL Case Statement, SQL Cast, SQL Convert, SQL Datetime, SQL Isdate