date comparison in where clause makes view show error

[DiverMarv]

I have a view:

CREATE VIEW table_view
AS 	SELECT	USERID, LEOTYP, LEONUM, LEOST, ACTIVE_FLAG
		, CAST(LEOEXM + '/' + LEOEXD + '/' + LEOEXY AS DATETIME) AS EXPIRES
	FROM	table
	WHERE	ISDATE(LEOEXM + '/' + LEOEXD + '/' + LEOEXY) = 1
GO

That only gets items that have a valid date. Querying the view shows that only valid dates are there...
But if I have a query that does a date comparison in the where clause (example)

select USERID, LEOTYP, LEONUM, LEOST, ACTIVE_FLAG, EXPIRES
from table_view
where USERID = 13549 and EXPIRES <= '12/30/2005'

This query bombs if any of the users dates in the original table are either blank, or partial (we allow entry of date parts in 3 form fields, then we put them back together)

 

[vongrunt]

And partial dates, are they considered valid or not?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[DiverMarv]

The ISDATE() function is part of sql server. '6/2005' is not a date, but '6/21/2005' is, and '2006' is.

Oh, and the error I'm receiving is:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

 

[vongrunt]

Problem is, server simply merges execution plan for view and query - there are no guarantees WHERE ISDATE() will always be evaluated first. So I'm not sure using view for cleaning "syntactically incorrect" data is good idea.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLBill]

A lot of times, that error is returned because the date values are not year first, then month, then day.

SQL Server has to know how to interpret dates. 1/2/2006 could be January 2, 2006 OR 1 February 2006. So, 12/30/2005 could be December 30, 2005 OR the 12th day of the 30th month (whoops, conversion error).

Use SET DATEFORMAT MDY before your SELECT statement to tell SQL Server the dates are in Month, Day, Year format.

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

Even if there are no out-of range dates, problem still persists:

create table blah (d varchar(2), m varchar(2), y varchar(4))
go
create view blahview as
select cast(m + '/' + d + '/' + y as datetime) as expires
from blah
where isdate(m + '/' + d + '/' + y) = 1
go

insert into blah values (2, 4, 2005)
insert into blah values (2, 4, '')

-- works
select * from blahview
-- crashes
select * from blahview where expires < getdate()

drop view blahview
drop table blah

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]