can't filter a view (datetype conversion error)

Question

I have a view for which I'm trying to query.

Select top 100 Expiration , year(Expiration) from CICPROD.ExpiredLots

--where year(Expiration) = 2017

which returns (when I edit out the WHERE part):

Expiration  (No column name)
2017-09-10  2017
2021-06-20  2021
2017-01-16  2017
2017-01-04  2017
2017-08-22  2017
2017-01-25  2017
2021-07-18  2021
2017-04-28  2017
2017-09-14  2017
2017-01-04  2017
2010-06-10  2010
2020-04-24  2020
2019-03-03  2019
2020-09-11  2020
2020-06-10  2020
2020-03-26  2020
2020-07-14  2020
2017-05-13  2017
2018-02-16  2018
2015-05-25  2015
2015-08-29  2015
2016-04-04  2016
2017-03-31  2017
2017-03-31  2017
2017-03-31  2017
2015-08-15  2015
2018-02-27  2018
2018-02-16  2018
2016-01-31  2016
2017-03-31  2017
2014-02-01  2014
2018-08-09  2018
2007-08-01  2007
2017-05-27  2017
2020-12-15  2020
2012-03-31  2012
2012-03-22  2012
2016-01-05  2016
2018-01-10  2018
2013-03-05  2013
2015-08-05  2015
2017-11-30  2017
2013-06-12  2013
2019-11-22  2019
2013-04-27  2013
2016-04-17  2016
2018-01-10  2018
2018-02-16  2018
2018-01-10  2018
2018-02-16  2018
2016-04-30  2016
2020-01-05  2020
2016-12-21  2016
2017-11-08  2017
2018-01-10  2018
2014-09-14  2014
2018-01-10  2018
2016-06-25  2016
2014-01-31  2014
2020-03-20  2020
2017-02-15  2017
2016-02-01  2016
2015-08-05  2015
2016-03-24  2016
2013-08-28  2013
2016-09-08  2016
2018-02-16  2018
2014-12-09  2014
2017-08-13  2017
2018-01-10  2018
2016-10-23  2016
2018-02-17  2018
2009-05-28  2009
2017-07-12  2017
2017-03-31  2017
2016-04-23  2016
2015-04-11  2015
2018-01-10  2018
2017-11-17  2017
2018-01-10  2018
2017-11-08  2017
2017-11-08  2017
2017-03-31  2017
2017-03-31  2017
2017-10-02  2017
2011-05-03  2011
2010-12-10  2010
2014-11-14  2014
2017-08-17  2017
2015-06-30  2015
2017-10-12  2017
2016-03-23  2016
2018-05-10  2018
2017-08-17  2017
2017-01-01  2017
2015-12-19  2015
2016-02-28  2016
2018-02-27  2018
2017-07-07  2017
2016-09-08  2016

However, when I try to filter the where with column 2 to say 2017, I get the error message:

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

But when I tried it with TOP 10, the query WORKED no PROBLEM!!

I checked the length of the field and they're all 10 and have the same format so I'm wondering why this is happening.

Can anyone assist??

Original Query is:

Select cast([STOLOTFCY].ITMREF_0 as varchar(20)) as 'Product', 

[ITMMASTER].ITMDES1_0 as 'Desc1',  

[STOLOTFCY].STOFCY_0 as Site, cast([STOLOTFCY].LOT_0 as varchar(30)) as Lot ,

 [STOCK].STA_0 as Status,

( case when isdate([STOLOT].USRFLD1_0) = 0 then null else 

convert(date,[STOLOT].USRFLD1_0,101) end)  as Expiration, 

[STOCK].QTYSTU_0 as 'Total Stk', 

  [ITMMASTER].STU_0 as 'STK', [STOLOTFCY].AVC_0 as 'avgcost' ,

  [STOLOTFCY].AVC_0 * [STOCK].QTYSTU_0 as 'ExtendedValue' , 

  cast([STOLOT].LOTCREDAT_0 as date) as 'Lotcreated', 

  [ITMMASTER].ITMWEI_0 * [STOCK].QTYSTU_0 as 'TotalWgt(Kg)'
 from [CICPROD].[STOLOTFCY] 
inner join [CICPROD].[ITMMASTER] on  [STOLOTFCY].ITMREF_0 = [ITMMASTER].ITMREF_0
inner join [CICPROD].[STOLOT] on [STOLOT].ITMREF_0 = [STOLOTFCY].ITMREF_0 and [STOLOT].LOT_0 = [STOLOTFCY].LOT_0
inner join [CICPROD].[STOCK] on [STOCK].ITMREF_0 = [STOLOTFCY].ITMREF_0 and [STOLOTFCY].STOFCY_0 = [STOCK].STOFCY_0 and [STOCK].LOT_0 =
[STOLOTFCY].LOT_0 and [STOLOTFCY].SLO_0 = [STOCK].SLO_0
where [STOLOTFCY].[AAACUMQTY_0] + [STOLOTFCY].[QQQCUMQTY_0] + [STOLOTFCY].[RRRCUMQTY_0] > 0

Answer 1

Based on the discussion via comments i think the following code should help you find the data which is causing the query to fail.

SET DATEFORMAT mdy;
select
[STOLOT].USRFLD1_0, *
from
CICPROD.STOLOT
WHERE
ISDATE([STOLOT].USRFLD1_0)= 0 and [STOLOT].USRFLD1_0 is not null

Answer 2

Try this:

Select top 100 Expiration , year(Expiration) from CICPROD.ExpiredLots
where year(cast(Expiration as date)) = 2017