原因

一个很好的故障排除步骤是查看date函数返回的值。例如，可以执行以下SELECT语句：

SELECT DATEADD(mm,-6,(DATEPART(m,GETDATE())))

这将返回一个日期1899-07-02 00:00:00:00，它实际上将WHERE子句转换为：
WHERE [DateField] BETWEEN '1899-07-02' AND '2023-01-20
这就解释了查询返回表中所有记录的原因。
溶液
首先，可以将当前日期截断为当前月份的第一天：

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FOM;

输出：2023-01-01 00:00:00

现在添加另一个DATEADD函数以减去六个月：

SELECT DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS FOM_SIX_MNTHS_AGO;

输出：2022-07-01 00:00:00
把一切放在一起

SELECT * 
FROM [TABLE_NAME] 
WHERE [DateField] BETWEEN DATEADD(MONTH, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND GETDATE();

对于日历表（或者函数，如果您不想具体化实际的表）来说，这是一个很好的用例。
考虑：

CREATE OR ALTER FUNCTION Calendar (@StartYear INT, @EndYear INT)
RETURNS @Calender TABLE (
Date DATE, DateEndTime DATETIME2, Year INT, Month INT, Day INT, Quarter INT, WeekNumber INT, MonthName NVARCHAR(20), DayName NVARCHAR(20), WeekStartDate DATE, WeekEndDate DATE, MonthStartDate DATE, MonthEndDate DATE,
 QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
 YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT) 
AS
BEGIN

WITH CalendarHistory AS (
SELECT CAST(GETUTCDATE() AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,-1,Date)
  FROM CalendarHistory
 WHERE DATEADD(DAY,-1,Date) > DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
), CalendarFuture AS (
SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,1,Date)
  FROM CalendarFuture
 WHERE DATEADD(DAY,1,Date) < DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
), Calendar AS (
SELECT Date
  FROM CalendarHistory
UNION ALL
SELECT Date
  FROM CalendarFuture
)

INSERT INTO @Calender
SELECT Date, 
       DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
       DATEPART(YEAR,Date) AS Year, DATEPART(MONTH,Date) AS Month, DATEPART(DAY,Date) AS Day, DATEPART(QUARTER,Date) AS Quarter, DATEPART(WEEK,Date) AS WeekNumber,
       DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
       DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
       DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
       DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS QuarterStartDate, DATEADD(DAY,-1,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS QuarterEndDate,
       DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
       CAST(DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS DATETIME2) AS WeekStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date)) AS DATETIME2)) AS WeekEndTime,
       CAST(DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS DATETIME2) AS MonthStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS DATETIME2)) AS MonthEndTime,
       CAST(DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS DATETIME2) AS QuarterStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS DATETIME2)) AS QuarterEndTime,
       CAST(DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS DATETIME2) AS YearStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS DATETIME2)) AS YearEndTime,
       CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 1 ELSE 0 END AS IsWeekDay
  FROM Calendar
 ORDER BY Date
 OPTION (MAXRECURSION 0)
 RETURN
END;

GO

然后可以这样使用：

SELECT *
  FROM dbo.Calendar(1,1) 
 ORDER BY Date

在本例中，我通过为@StartYear和@EndYear传递1来请求当前日期之前的一年和之后的一年

Date        DateEndTime                 Year    Month   Day Quarter WeekNumber  MonthName   DayName     WeekStartDate   WeekEndDate MonthStartDate  MonthEndDate    QuarterStartDate    QuarterEndDate  YearStartDate   YearEndDate WeekStartTime               WeekEndTime                 MonthStartTime              MonthEndTime                QuarterStartTime            QuarterEndTime              YearStartTime               YearEndTime                 IsWeekDay
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2022-01-01  2022-01-01 23:59:59.9999970 2022    1       1   1       1           January     Saturday    2021-12-26      2022-01-01  2022-01-01      2022-01-31      2022-01-01          2022-03-31      2022-01-01      2022-12-31  2021-12-26 00:00:00.0000000 2022-01-01 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-01-31 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-03-31 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-12-31 23:59:59.9999970 1
2022-01-02  2022-01-02 23:59:59.9999970 2022    1       2   1       2           January     Sunday      2022-01-02      2022-01-08  2022-01-01      2022-01-31      2022-01-01          2022-03-31      2022-01-01      2022-12-31  2022-01-02 00:00:00.0000000 2022-01-08 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-01-31 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-03-31 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-12-31 23:59:59.9999970 1
2022-01-03  2022-01-03 23:59:59.9999970 2022    1       3   1       2           January     Monday      2022-01-02      2022-01-08  2022-01-01      2022-01-31      2022-01-01          2022-03-31      2022-01-01      2022-12-31  2022-01-02 00:00:00.0000000 2022-01-08 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-01-31 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-03-31 23:59:59.9999970 2022-01-01 00:00:00.0000000 2022-12-31 23:59:59.9999970 0
....
2023-12-29  2023-12-29 23:59:59.9999970 2023    12      29  4       52          December    Friday      2023-12-24      2023-12-30  2023-12-01      2023-12-31      2023-10-01          2023-12-31      2023-01-01      2023-12-31  2023-12-24 00:00:00.0000000 2023-12-30 23:59:59.9999970 2023-12-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 2023-10-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 2023-01-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 0
2023-12-30  2023-12-30 23:59:59.9999970 2023    12      30  4       52          December    Saturday    2023-12-24      2023-12-30  2023-12-01      2023-12-31      2023-10-01          2023-12-31      2023-01-01      2023-12-31  2023-12-24 00:00:00.0000000 2023-12-30 23:59:59.9999970 2023-12-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 2023-10-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 2023-01-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 1
2023-12-31  2023-12-31 23:59:59.9999970 2023    12      31  4       53          December    Sunday      2023-12-31      2024-01-06  2023-12-01      2023-12-31      2023-10-01          2023-12-31      2023-01-01      2023-12-31  2023-12-31 00:00:00.0000000 2024-01-06 23:59:59.9999970 2023-12-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 2023-10-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 2023-01-01 00:00:00.0000000 2023-12-31 23:59:59.9999970 1

然后，您可以通过从查询联接到它来使用它：

SELECT *
  FROM MyOrders m
    INNER JOIN dbo.Calendar(1,1) c
      ON m.OrderDate = c.Date
    INNER JOIN MyOrders d
      ON d.OrderDate BETWEEN c.MonthStartDate AND c.MonthEndTime
 WHERE m.OrderID = 1

如果您使用的是SQL Server 2012或更高版本，则可以使用EOMONTH函数，如下所示。

WHERE [DateField] BETWEEN DATEADD(day, 1, EOMONTH(GETDATE(), -7)) AND GETDATE()

就我个人而言，我喜欢把这类事情分解成变量，我从来不使用between。

-- By declaring variables I know they are dates not datetimes
-- `getdate()` returns a datetime which can cause issues with datetime windows
declare @StartDate date, @EndDate date = getdate();
-- By setting @startdate relative to @EndDate I can test different date windows easily
set @EndDate dateadd(d, 1, eomonth(@StartDate, -7));

select *
from dbo.MyTable
-- This form of window handles date and datetimes correctly without any guessing
-- I find `between` to be unintuitive
where DateColumn >= @StartDate
and DateColumn < dateadd(day, 1, @EndDate);