sql query out of range datetime value

[raam]

create proc getproviders
as
begin
select ProviderType,ProviderTypeId,ProviderName,City,Stat e,Country,
StagingName,providerCode,convert(varchar(10),Agree ment_enddate,101)Agreement_enddate from provider
join stagingMaster on
stagingMaster.StagingId = provider.StagingId
where isDate(Agreement_enddate) = 1 and convert(datetime,Agreement_enddate,101) <= getdate() and logicallyDeletedFlag=0
end

iam getting the following error can any one suggest

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

thanks & regards
raam

[code green]

Not sure where the error is but I think it is this line

convert(datetime,Agreement_enddate,101)

Test by removing that line.
I can't see why you are converting to a datetime.
Surely it already exists as such.
To remove any ambiguity rename the alias 'Agreement_enddate' to something else.

[ck9663]

What's the data type of this column: Agreement_enddate

-- CK