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.
CodePudding user response：
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
CodePudding user response：
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();
CodePudding user response：
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);