SQL技术问询：如何按当前日期筛选指定两列（昨日至明日范围）

Got it, let's tweak your query to add the date range filter you need. The key is to check if either datPromisedDelivery or datCommisioned falls between yesterday and tomorrow (inclusive), while keeping your existing filters intact.

Here's the updated query with the new date condition integrated:

SELECT 
    nJobNumber, 
    strClientName, 
    strClientReference, 
    strJobCategory, 
    datCommisioned, 
    datPromisedDelivery 
FROM vwJobBookForXML 
WHERE 
    bCompleted = 0 
    AND nJobNumber IS NOT NULL 
    AND strClientReference IS NOT NULL 
    -- Date filter for yesterday, today, and tomorrow
    AND (
        datPromisedDelivery BETWEEN DATEADD(day, -1, CAST(GETDATE() AS DATE)) 
                                AND DATEADD(day, 1, CAST(GETDATE() AS DATE))
        OR 
        datCommisioned BETWEEN DATEADD(day, -1, CAST(GETDATE() AS DATE)) 
                          AND DATEADD(day, 1, CAST(GETDATE() AS DATE))
    )

Quick breakdown of the new logic: #

• CAST(GETDATE() AS DATE) strips off the time component from the current date, so we're comparing full days (no need to worry about time-of-day mismatches). If your date columns are already DATE type (not datetime), you can skip this cast.
• The BETWEEN operator includes both the start (yesterday) and end (tomorrow) dates, so it captures all rows where either date falls in that 3-day window.
• We wrap the two date checks in parentheses to make sure they're evaluated as a group—this ensures we keep your existing AND filters while checking if either date meets the range condition.

For other SQL dialects (if needed): #

If you're using PostgreSQL or MySQL instead of SQL Server, adjust the date functions like this:

-- PostgreSQL version
AND (
    datPromisedDelivery BETWEEN CURRENT_DATE - INTERVAL '1 day' AND CURRENT_DATE + INTERVAL '1 day'
    OR datCommisioned BETWEEN CURRENT_DATE - INTERVAL '1 day' AND CURRENT_DATE + INTERVAL '1 day'
)

-- MySQL version
AND (
    datPromisedDelivery BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)
    OR datCommisioned BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)
)

This should give you exactly the rows you're looking for—jobs where either the promised delivery or commissioned date is within the yesterday-to-tomorrow window, alongside your existing filters.

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