SQL Server日期列查询异常：筛选预计到货早于预计就绪行结果错误

Got it, let's break down why you're getting 5 incorrect results instead of the expected 1, and fix this step by step.

First: Fix the Typo #

Your WHERE clause has a critical typo: ETA Ready should be ETA_Ready (missing the underscore). While you flagged this as a suspected error, correcting it is essential to avoid column-not-found errors or unintended behavior.

Core Issue: Comparing Datetime vs. Date Values #

The main problem here is a mismatch between what you're displaying and what you're filtering. You convert ETA_Arrive and ETA_Ready to date format for your SELECT output, but your WHERE clause still compares the original datetime columns.

For example, if you have a row where:

• ETA_Arrive = 2020-07-15 09:00:00
• ETA_Ready = 2020-07-15 14:00:00

The datetime comparison ETA_Arrive < ETA_Ready will return true (since 9 AM is earlier than 2 PM), but their date values are identical. This is why you're getting extra rows that don't actually meet your "arrival date earlier than ready date" requirement.

Correct Query #

To fix this, you need to compare the converted date values in your WHERE clause, not the original datetime columns. Here are two clean, readable ways to do this:

Option 1: Convert Directly in the WHERE Clause #

SELECT 
    Est_Arrive = CONVERT(date, ETA_Arrive), 
    Est_Ready = CONVERT(date, ETA_Ready)
FROM [YourActualTableName] -- Replace with your real table name
WHERE CONVERT(date, ETA_Arrive) < CONVERT(date, ETA_Ready)

Option 2: Use a CTE for Clarity #

If you prefer code that's easier to maintain, a Common Table Expression (CTE) lets you define the date conversions once, then filter on the converted values:

WITH ConvertedDates AS (
    SELECT 
        CONVERT(date, ETA_Arrive) AS Est_Arrive,
        CONVERT(date, ETA_Ready) AS Est_Ready
    FROM [YourActualTableName] -- Replace with your real table name
)
SELECT Est_Arrive, Est_Ready
FROM ConvertedDates
WHERE Est_Arrive < Est_Ready

Both of these queries will only return rows where the date of arrival is strictly earlier than the date of readiness, which should give you exactly the 1 correct row you expect.

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