Sql 如果当前日期是一周中的某一天，则从另一天提取结果

老实说，为了可读性，日期上升参数很可能是better.不过，你仍然可以在同一批中完成这项工作.

我使用了一种有点"绕来绕go "的方法(参见answerxGreg Hewgill)来判断哪一天是工作日，因为我想确保给出的结果与语言/DATEFIRST设置的regardless相同:

DECLARE @DateFrom date = CASE (DATEPART(WEEKDAY,GETDATE()) + @@DATEFIRST + 5) % 7 + 1 WHEN 1 THEN DATEADD(DAY, -3,GETDATE()) ELSE DATEADD(DAY, -1,GETDATE()) END,
        @Dateto date = GETDATE(); --Considering there is no data on a Sunday, this is "fine"

--SELECT @DateFrom, @Dateto

SELECT [a bunch of demographic and appointment-related fields]
FROM dbo.Demographics D
     JOIN dbo.ApptInfo AI ON D.PatientID = AI.PatientID
WHERE AI.ApptDate >= @DateFrom
  AND AI.ApptDate < @Dateto;

如果您"必须"在没有变量的情况下完成它，那么它将如下所示:

SELECT [a bunch of demographic and appointment-related fields]
FROM dbo.Demographics D
     JOIN dbo.ApptInfo AI ON D.PatientID = AI.PatientID
WHERE AI.ApptDate >= CONVERT(date,CASE (DATEPART(WEEKDAY,GETDATE()) + @@DATEFIRST + 5) % 7 + 1 WHEN 1 THEN DATEADD(DAY, -3,GETDATE())
                                                                                                ELSE DATEADD(DAY, -1,GETDATE())
                                  END)
  AND AI.ApptDate < CONVERT(date,GETDATE());