Pull Datediff without year in SQL Server

Question

In SQL Server, I want to pull a List of dates that are 2 days before/30 days after current date, but ONLY the month/Day matter, not the year.

For instance today is 10/31/2023

I would want 10/30/1980 included as well as 11/25/2011, etc.

I can't find a good way to do this.

Answer 1

You can make use of a "dates" table containing a row-per-day for the range of years in question. Once you've generated this table, you can use it for multiple purposes. An example should help show what I mean.

DROP TABLE IF EXISTS [dbo].[dates];
CREATE TABLE [dbo].[dates]
(
    [date] date NOT NULL
        PRIMARY KEY
        CLUSTERED
);

This fills the [dates] table with rows between the start of 1980 and the end of 2079.

DECLARE @top int = CONVERT(int, 100*365.25, 0);

INSERT INTO [dbo].[dates]
(
    [date]
)
SELECT TOP(@top)
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '1980-01-01')
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

The TOP clause determines the number of rows by considering 100 years and 365.25 days per year, which accounts roughly for leap years.

So, assuming we're interested in returning rows that are from 10 days before March 4th, up to 20 days after March 4th, we'd use this query:

DECLARE @target_date date = CONVERT(date, '2027-03-04', 126);
DROP TABLE IF EXISTS #days;
CREATE TABLE #days
(
    [date] date NOT NULL
        PRIMARY KEY
        CLUSTERED
);

INSERT INTO #days 
(
    [date]
)
SELECT [d].[date]
FROM [dbo].[dates] [d]
CROSS APPLY (VALUES (MONTH([d].[date]), DAY([d].[date]))) [days]([month],[day])
WHERE [days].[month] = MONTH(@target_date)
    AND [days].[day] = DAY(@target_date)


SELECT [d].[date]
FROM [dbo].[dates] [d]
    INNER JOIN #days ON [d].[date] >= DATEADD(DAY, -10, #days.[date])
                        AND [d].[date] <= DATEADD(DAY, 20, #days.[date]);

In the above query, we're using a CROSS APPLY clause to obtain the month and day-of-month for each date in the [dbo].[dates] table, which we limit via the WHERE clause to the day we centered around. The results are inserted into a temporary table which we then use in the output query with an INNER JOIN clause performing date math via the DATEADD function.

Here's a sample from the output:

date
1980-02-23
1980-02-24
1980-02-25
1980-02-26
1980-02-27
1980-02-28
1980-02-29
1980-03-01
1980-03-02
1980-03-03
1980-03-04
1980-03-05
1980-03-06
1980-03-07
1980-03-08
1980-03-09
1980-03-10
1980-03-11
1980-03-12
1980-03-13
1980-03-14
1980-03-15
1980-03-16
1980-03-17
1980-03-18
1980-03-19
1980-03-20
1980-03-21
1980-03-22
1980-03-23
1980-03-24
1981-02-22
1981-02-23
1981-02-24
1981-02-25
1981-02-26
1981-02-27
1981-02-28
1981-03-01
1981-03-02
1981-03-03
1981-03-04
1981-03-05
1981-03-06
1981-03-07
1981-03-08
1981-03-09
1981-03-10
1981-03-11
1981-03-12
1981-03-13
1981-03-14
1981-03-15
1981-03-16
1981-03-17
1981-03-18
1981-03-19
1981-03-20
1981-03-21
1981-03-22
1981-03-23
1981-03-24
1982-02-22
1982-02-23
1982-02-24
1982-02-25
1982-02-26
1982-02-27
1982-02-28
1982-03-01
1982-03-02
1982-03-03
1982-03-04
1982-03-05
1982-03-06
1982-03-07
1982-03-08
1982-03-09
1982-03-10
1982-03-11
1982-03-12
1982-03-13
1982-03-14
1982-03-15
1982-03-16
1982-03-17
1982-03-18
1982-03-19
1982-03-20
1982-03-21
1982-03-22
1982-03-23
1982-03-24
1983-02-22
.
.
.
2077-03-24
2078-02-22
2078-02-23
2078-02-24
2078-02-25
2078-02-26
2078-02-27
2078-02-28
2078-03-01
2078-03-02
2078-03-03
2078-03-04
2078-03-05
2078-03-06
2078-03-07
2078-03-08
2078-03-09
2078-03-10
2078-03-11
2078-03-12
2078-03-13
2078-03-14
2078-03-15
2078-03-16
2078-03-17
2078-03-18
2078-03-19
2078-03-20
2078-03-21
2078-03-22
2078-03-23
2078-03-24
2079-02-22
2079-02-23
2079-02-24
2079-02-25
2079-02-26
2079-02-27
2079-02-28
2079-03-01
2079-03-02
2079-03-03
2079-03-04
2079-03-05
2079-03-06
2079-03-07
2079-03-08
2079-03-09
2079-03-10
2079-03-11
2079-03-12
2079-03-13
2079-03-14
2079-03-15
2079-03-16
2079-03-17
2079-03-18
2079-03-19
2079-03-20
2079-03-21
2079-03-22
2079-03-23
2079-03-24

I chose to use "10 days before and 20 days after" as the rules in my query, but that would be trivially easy to adjust to 2 days before and 10 days afterwards.

A word of caution here, dealing with leap years, and historical dates can be fraught with difficulty, so testing the output to ensure it works correctly for your needs is paramount.

And, just because I like to be pedantic, use ISO8601 format dates (or as close to that format as required). Doing so will ensure SQL Server never makes assumptions about your date format that you didn't intend.

Answer 2

create table #dt (d date)

insert into #dt (d) values ('20000101')
insert into #dt (d) values ('20031030')
insert into #dt (d) values ('20231030')
insert into #dt (d) values ('20201029')
insert into #dt (d) values ('20241125')


--declare @today date='20231231'

declare @today date='20230101'

declare @baseyear char(4)=cast(year(@today) as char(4))

print @baseyear

declare @dfrom date = dateadd(DD,-2,@today)
declare @dto date = dateadd(DD,30,@today)

print @dfrom 
print @dto


select d,@baseyear+FORMAT(d,'MMdd') from #dt  where cast(@baseyear+FORMAT(d,'MMdd') as date) between @dfrom and @dto