T-SQL 将 where 子句应用于不起作用的函数字段

Question

我很难按CreateDate过滤此视图。表中的 CreateDate 采用以下格式：2013-10-14 15：53：33.900

我设法将年月和日DATEPART到单独的列中，但现在它不允许我在那些新创建的列上使用我的WHERE子句。具体来说，错误是两行的"无效的列名称创建年份"。伙计们，我在这里做错了什么？有没有比解析日、月和年更好/更简单的方法？这似乎矫枉过正。我花了很多时间在这上面无济于事。

SELECT convert(varchar, DATEPART(month,v.CreateDate)) CreateMonth, 
convert(varchar, DATEPART(DAY,v.CreateDate)) CreateDay, 
convert(varchar, DATEPART(YEAR,v.CreateDate)) CreateYear, 
v.CreateDate, 
v.customerName 
From 
vw_Name_SQL_DailyPartsUsage v  
full outer join 
ABC.serviceteamstechnicians t on v.TechnicianNumber = t.AgentNumber 
full outer join 
ABC.ServiceTeams s on t.STID = s.STID
where 
CreateYear >= '02/01/2018' 
and 
CreateYear <= '02/20/2018'

Answer 1

不能从 where 中的选择中引用别名

即使你可以，为什么你会期望一年是'02/01/2018'

你为什么要转换为瓦尔查尔

where year(v.CreateDate) = 2018 

或

select crdate, cast(crdate as date), year(crdate), month(crdate), day(crdate)   
from sysObjects 
where cast(crdate as date) <= '2014-2-20'
and cast(crdate as date) >= '2000-2-10' 
order by crdate

Answer 2

您可以使用：

SELECT convert(varchar, DATEPART(month,v.CreateDate)) CreateMonth, 
convert(varchar, DATEPART(DAY,v.CreateDate)) CreateDay, 
convert(varchar, DATEPART(YEAR,v.CreateDate)) CreateYear, 
v.CreateDate, 
v.customerName 
From  vw_Name_SQL_DailyPartsUsage v  
full outer join 
ABC.serviceteamstechnicians t on v.TechnicianNumber = t.AgentNumber 
full outer join 
ABC.ServiceTeams s on t.STID = s.STID
where CreateDate BETWEEN '20180102' and  '20180220';

有关逻辑查询处理的详细信息是，如果不使用子查询/交叉应用，则无法在WHERE子句中引用SELECT的列别名。