SQL日期参数跳过周末问题：新手查询前一日开户数据遇周一异常

嘿，我来帮你排查这个问题！你的核心需求应该是获取上一个工作日的新开户账户——也就是周二到周五查前一天的，周一的时候查上周五的（而不是周日的），对吧？

先看你现有代码的问题：

• 你用了IIF(DATEPART(dw, GETDATE()) = 2, b.startdate > GETDATE() - 3, b.startdate > GETDATE()-1)，这里有两个关键问题：
  1. GETDATE()是带时间戳的，比如今天周一10号上午10点，GETDATE()-3是周六8号上午10点，b.startdate > 8号10点会包含周日9号全天和周一10号上午10点前的记录，这就是你周一查询时拿到周日数据的直接原因。
  2. DATEPART(dw, GETDATE())的返回值依赖SQL Server的DATEFIRST设置（默认可能周日是1，周一是2），如果服务器设置改动，这个判断会直接失效，不够稳健。

修正方案 #

我们可以调整日期计算逻辑，同时规避时间戳和服务器设置的影响：

步骤1：明确“上一个工作日”的计算规则

• 如果今天是周一，上一个工作日为上周五（往前推3天）
• 如果是周二到周五，上一个工作日为昨天（往前推1天）

步骤2：修正后的完整SQL

-- 先计算上一个工作日的纯日期（去掉时间戳）
DECLARE @PrevWorkDay DATE
SET @PrevWorkDay = CASE DATENAME(weekday, GETDATE())
    WHEN 'Monday' THEN DATEADD(day, -3, CAST(GETDATE() AS DATE))
    ELSE DATEADD(day, -1, CAST(GETDATE() AS DATE))
END

SELECT 
    PortfolioCode AS "Account",
    f.reportheading1 AS "Name",
    d.portfoliostatus AS "Status",
    e.investmentstrategy AS "Strategy",
    e.brokersource AS "Broker",
    e.referringfirm AS "RefBy",
    e.custodian AS "Bank",
    e.manager AS "Manager",
    e.administrator AS "APM",
    d.initialvalue AS "Open Value",
    b.StartDate AS "Start Date",
    e.percentcash AS "Cash%",
    e.targetmvequity AS "Eqt Trgt",
    e.targetmvfixed AS "FI Trgt",
    e.munistrategy AS "Muni Strat",
    e.nationalmuni AS "Nat. Muni",
    e.statemunistrategy AS "State Strat.",
    e.residency AS "Residency"
FROM AdvPortfolioBaseExt AS A
INNER JOIN AdvPortfoliobase AS B ON A.PortfolioBaseID = b.PortfolioBaseID
INNER JOIN advportfolio AS C ON A.PortfolioBaseID = c.PortfolioID
INNER JOIN advapp.vportfolio AS D ON A.PortfolioBaseID = D.PortfolioID
INNER JOIN advportfoliobaseext AS E ON A.portfoliobaseid = e.portfoliobaseID
INNER JOIN advapp.vportfoliobase AS F ON a.PortfolioBaseID = f.PortfolioBaseID
WHERE 
    c.portfoliostatus <> 'Closed'
    -- 筛选上一个工作日的完整日期区间，避免时间干扰
    AND b.startdate >= @PrevWorkDay
    AND b.startdate < CAST(GETDATE() AS DATE)

额外优化（可选） #

如果你的SQL Server版本是2012及以上，可以把变量部分改成内联CASE语句，不用单独声明变量：

WHERE 
    c.portfoliostatus <> 'Closed'
    AND b.startdate >= CASE DATENAME(weekday, GETDATE())
        WHEN 'Monday' THEN DATEADD(day, -3, CAST(GETDATE() AS DATE))
        ELSE DATEADD(day, -1, CAST(GETDATE() AS DATE))
    END
    AND b.startdate < CAST(GETDATE() AS DATE)

这样调整后，周一查询时只会获取上周五的开户记录，周二到周五获取前一天的，完全符合你的需求啦！

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