无法筛选视图(日期类型转换错误)

Question

我有一个视图，我正在尝试查询。

Select top 100 Expiration , year(Expiration) from CICPROD.ExpiredLots
--where year(Expiration) = 2017

返回(当我编辑 WHERE 部分时(：

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

但是，当我尝试使用第 2 列过滤 where 以表示 2017 时，我收到错误消息：

Msg 241，级别 16，状态 1，第 1 行 从字符串转换日期和/或时间时转换失败。

但是当我尝试使用 TOP 10 时，查询没有问题！！

我检查了字段的长度，它们都是 10 并且格式相同，所以我想知道为什么会发生这种情况。

谁能帮忙？？

原始查询为：

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

根据通过注释进行的讨论，我认为以下代码应该可以帮助您找到导致查询失败的数据。

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

试试这个：

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