SQL query to find users for monthly anniversary

Question

I'm attempting to write a query that find users who are celebrating a monthly anniversary with my company so we can trigger an email to them (i.e., if today's date is 3/27, it would find people who signed up on 1/27, 2/27, 3/27, 4/27, etc., regardless of year).

My method is to find users whose "day" part of their sign-up date equals the "day" part of today's date.

SELECT [User Id],[Sign Up Date] 
  FROM [Monthly Account Update] 
 WHERE DATEPART(DAY,[Sign Up Date]) = DAY(GETDATE())

However, this of course doesn't work for these scenarios:

1. At the end of February, I wouldn't get the chance to email people with 29, 30 and 31 monthly anniversary days, as the don't happen. Ideally, when it's February 28th, I'd just like to lump in people with 29, 30 and 31 dates.

2. For every other month where the date ends in a 30th, I wouldn't get to email people with 31st dates. Ideally here, I'd just like to lump in people with 30 and 31 dates.

Can either of those things be accomplished with a SQL query?

Answer 1

Some complicated answers here! We can simplify greatly:

create function dbo.fnIsMonthlyAnniversary(
    @startDate date,
    @comparisonDate date
)
returns bit
as
begin
    declare @retVal bit
    set @retVal = 0

    declare @deltaMonth int

    set @deltaMonth = datediff( mm, @startDate, @comparisonDate )

    if dateadd( mm, @deltaMonth, @startDate ) = @comparisonDate
    begin
        set @retVal = 1
    end

    return @retVal
end

-- usage:
select dbo.fnIsMonthlyAnniversary( '2012-12-31', CONVERT( date, getdate() ) )

for your usage:

SELECT [User Id],[Sign Up Date] 
  FROM [Monthly Account Update] 
 WHERE 1 = dbo.fnIsMonthlyAnniversary( [Sign Up Date], CONVERT( date, getdate() ) )