SQL查询昨日日期行提示‘Invalid column name’的解决方案

你的错误根源是SQL执行顺序规则：WHERE子句的执行优先级高于SELECT子句，当数据库处理WHERE条件时，SELECT里定义的YesterdayDate别名还未被创建，因此会抛出「无效列名」的错误。

下面是几种可行的修正方案：

方案1：直接在WHERE子句中重复日期转换逻辑 #

这是最简洁的写法，直接对原字段做转换后和昨日日期对比：

SELECT CAST(DateEnded AS date) AS DateEndedDate
FROM [dbo].[V]
WHERE CAST(DateEnded AS date) = DATEADD(DAY, -1, CAST(GETDATE() AS date));

方案2：使用子查询/CTE提升可读性 #

如果觉得重复写转换逻辑不够优雅，可以用子查询或CTE先预处理日期，再过滤：

子查询写法 #

SELECT DateEndedDate
FROM (
    SELECT CAST(DateEnded AS date) AS DateEndedDate
    FROM [dbo].[V]
) AS Temp
WHERE DateEndedDate = DATEADD(DAY, -1, CAST(GETDATE() AS date));

CTE写法 #

WITH ProcessedDates AS (
    SELECT CAST(DateEnded AS date) AS DateEndedDate
    FROM [dbo].[V]
)
SELECT DateEndedDate
FROM ProcessedDates
WHERE DateEndedDate = DATEADD(DAY, -1, CAST(GETDATE() AS date));

性能优化方案（适合大数据量场景） #

如果DateEnded列存在索引，直接使用CAST(DateEnded AS date)会导致索引失效，此时可以改用范围查询来利用索引，避免全表扫描：

SELECT CAST(DateEnded AS date) AS DateEndedDate
FROM [dbo].[V]
WHERE DateEnded >= DATEADD(DAY, -1, CAST(GETDATE() AS date))
  AND DateEnded < CAST(GETDATE() AS date);

这个写法的逻辑是：筛选出大于等于昨日0点、小于今日0点的所有datetime值，等价于日期为昨日的行，但能触发索引使用。

内容的提问来源于stack exchange，提问作者angleUr