WHERE date is between [the beginning of the month that was 6 months ago] and now

Question

I use the DATEADD function a lot when working with date ranges, especially if I'm working with rolling time periods. If I wanted to see all results from the past 6 months, I'd of course use...

WHERE [DateField] BETWEEN DATEADD(mm,-6,GETDATE()) AND GETDATE()

...and that would show me everything from July 20, 2022 to today, January 20, 2023.

But what If I wanted to see everything from the beginning of July? What if I wanted to see everything from the beginning of whatever month was 6 months ago, or whatever time period I specify, without having to manually plug in dates?

I tried the following...

WHERE [DateField] BETWEEN DATEADD(mm,-6,(DATEPART(m,GETDATE()))) AND GETDATE()

...in an attempt to get it to focus on the month, but for some reason now I'm suddenly getting all the results from this table I'm working with, with data going back to 2014. I think I'm on the right track in terms of logic, but something's obviously not quite right.

Does this answer your question? [How to get first and last day of previous month (with timestamp) in SQL Server](https://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server) — Thom A, Jan 20 '23 at 18:22

score 1 · Answer 1 · answered Jan 20 '23 at 19:02

This is a great use case for a calendar table (or function if you don't want to materialize a actual table).

Consider:

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

This can then be used like so:

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

In this case I've asked for a year before, and a year after the current date by passing 1 for both @StartYear and @EndYear

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

You can then use this by joining to it from your query:

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

score 1 · Accepted Answer · answered Jan 20 '23 at 19:14

The Why

A good troubleshooting step is to see the value your date function is returning. For example, you can execute this SELECT statement:

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

This returns a date of 1899-07-02 00:00:00:00 which effectively turns your WHERE clause into:

WHERE [DateField] BETWEEN '1899-07-02' AND '2023-01-20

Thus explaining why your query returned all the records in the table.

Solution

First, you can truncate the current date to the first day of the current month:

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

Output: 2023-01-01 00:00:00

Now add another DATEADD function to subtract six months:

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

Output: 2022-07-01 00:00:00

Putting it all together:

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

Love the thorough explanation! Thank you! – EJF Jan 21 '23 at 20:22 — EJF, Jan 21 '23 at 20:22

Dale K · Answer 3 · 2023-01-20T19:53:24.850

If you are using SQL Server 2012 or later you can utilise the EOMONTH function as follows.

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

Personally I like to split this sort of thing out into variables, and I never use between. So

-- 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);

WHERE date is between [the beginning of the month that was 6 months ago] and now

3 Answers3

The Why

Solution