SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

February 7, 2015 by Joey Blue Leave a Comment

How to Filter using SQL IsDate() Function and Cast

Video: SQL IsDate() Function as a Filter


Using the SQL IsDate() Function in your filter needs a little trick to make it work.

 

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 .

Related

Filed Under: SQL Tip Tagged With: SQL, SQL Case Statement, SQL Cast, SQL Convert, SQL Datetime, SQL Isdate

Leave a Question, Comment, or Reply. All are welcome!Cancel reply

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2025 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com