Comparing a real datetime to a constructed datetime

I have the following SQL:

select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a

where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.

The SQL above returns

2004-04-20 00:00:00.000 Deposit ...

Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

I get the following error:

Syntax error converting datetime from character string.

Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

If I do:

SELECT top 10 right(term,4) FROM dbo.status_view

I get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

But if I do:

SELECT top 10 right(term,4) FROM dbo.status_view
WHERE
isnumeric(right(term,4)) =0 and

It returns no rows! I think you're on to something. But the weirdest part is if I do:

SELECT top 10 cast(right(term,4) as int) FROM dbo.status_view

I still get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

Ack!|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

Oh wait, I was doing one and not zero. If I do:

SELECT count(1) FROM dbo.status_view
WHERE
and isnumeric(right(term,4)) = 0

it returns zero. So I guess thats not it...|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784

This works fine. It returns no rows, but no errors. This is driving me insane!|||This is your problem, right?

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

Which is syntactically incorrect...

what is a.submit_date? I assume it's from select *

Plus you need ro give the derived table a name

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) AS XXX
where dt >= submit_date

Does that run?