SFMC Birthday Audience SQL: Send Birthday Emails Daily

Build a daily SFMC birthday audience with SQL: the month and day match, a leap day fix for February 29, an anniversary variant and the automation setup.

You need to email customers on their birthday, and your SFMC audience has to rebuild itself every morning without anyone touching it. An SFMC birthday audience SQL query is the cleanest way to do it.

The short answer: match the month and day of a birthdate field against today's date with DATEPART() and GETDATE(), write the result to a sendable data extension with Overwrite, and run it daily in Automation Studio before your send.

‍

This post gives you the full query, a leap day fix for February 29 birthdays, an anniversary variant, and the automation setup. If you would rather describe the audience in words, you can also generate SFMC SQL from plain English.

‍

How an SFMC birthday audience works

‍

The data you need

You need one data extension that holds your customers, with a stable key, an email address, a first name and a real date field for the birthdate. In this post it is called Customers_Master, with SubscriberKey, EmailAddress, FirstName and BirthDate.

The birthdate must be a Date field, not text. If it arrives as text such as 14/03/1988, convert it at import time, because month and day logic on a text field breaks as soon as the format varies.

‍

The matching logic

A birthday is not an exact date match. Someone born in 1988 never has a birthdate equal to today, so you compare only the month and the day and ignore the year.

That is the whole trick: compare DATEPART(month, BirthDate) and DATEPART(day, BirthDate) with the same parts of GETDATE(). For more on date arithmetic, see our guide to SFMC SQL date functions.

‍

The SFMC birthday SQL query

‍

Query for today's birthdays

Paste this into a SQL Query activity in Automation Studio. It returns every customer whose birthday is today and who has an email address.

SELECT
  c.SubscriberKey,
  c.EmailAddress,
  c.FirstName,
  c.BirthDate
FROM Customers_Master c
WHERE c.EmailAddress IS NOT NULL
  AND c.BirthDate IS NOT NULL
  AND DATEPART(month, c.BirthDate) = DATEPART(month, GETDATE())
  AND DATEPART(day, c.BirthDate) = DATEPART(day, GETDATE())

Target data extension: Birthday_Today. Primary key: SubscriberKey. Data action: Overwrite, so yesterday's birthdays disappear every run.

‍

The query line by line

SELECT lists the four fields your email and journey need. Always name your columns, because the platform does not allow a join combined with SELECT *.
FROM Customers_Master c reads your customer data extension and gives it the short alias c.
The two IS NOT NULL conditions drop rows without an email address or without a birthdate, which would otherwise create blank sends or unpredictable matches.
The month condition compares the birth month with the current month.
The day condition compares the birth day with the current day. Both must be true for a match.

Keep comments out of the SQL itself. The Salesforce help states that open-ended comments such as a double hyphen are not supported in query activities, as listed in the SQL Reference in the Salesforce help.

‍

Handle leap day birthdays in SFMC SQL

‍

Why February 29 gets skipped

In a normal year there is no February 29, so the query above never matches anyone born on that date. Those customers silently miss their offer three years out of four.

The usual fix is to treat February 28 as their birthday in non-leap years. The query needs to know whether the current year is a leap year, which you can test with the modulus operator.

‍

Query with a leap day fallback

SELECT
  c.SubscriberKey,
  c.EmailAddress,
  c.FirstName,
  c.BirthDate
FROM Customers_Master c
WHERE c.EmailAddress IS NOT NULL
  AND c.BirthDate IS NOT NULL
  AND (
    (DATEPART(month, c.BirthDate) = DATEPART(month, GETDATE())
     AND DATEPART(day, c.BirthDate) = DATEPART(day, GETDATE()))
    OR
    (DATEPART(month, GETDATE()) = 2
     AND DATEPART(day, GETDATE()) = 28
     AND DATEPART(month, c.BirthDate) = 2
     AND DATEPART(day, c.BirthDate) = 29
     AND NOT (
       (DATEPART(year, GETDATE()) % 4 = 0 AND DATEPART(year, GETDATE()) % 100 <> 0)
       OR DATEPART(year, GETDATE()) % 400 = 0
     ))
  )

The first block is the normal match. The second block only fires on February 28, only for people born on February 29, and only when the current year is not a leap year. A year is a leap year when it is divisible by 4, except for century years that are not divisible by 400.

Test the logic in Query Studio or on a copy of your data before you automate it. The Salesforce help says SFMC SQL is based on SQL Server 2016 but does not match it exactly, so a quick test beats an assumption.

‍

Build an anniversary audience with the same pattern

‍

Query for signup or purchase anniversaries

Customer anniversaries work exactly like birthdays. Swap the birthdate for a signup date or a first purchase date, and add the number of years so your email can say "three years with us".

SELECT
  c.SubscriberKey,
  c.EmailAddress,
  c.FirstName,
  DATEDIFF(year, c.SignupDate, GETDATE()) AS AnniversaryYears
FROM Customers_Master c
WHERE c.EmailAddress IS NOT NULL
  AND c.SignupDate IS NOT NULL
  AND DATEPART(month, c.SignupDate) = DATEPART(month, GETDATE())
  AND DATEPART(day, c.SignupDate) = DATEPART(day, GETDATE())
  AND DATEDIFF(year, c.SignupDate, GETDATE()) >= 1

The last condition removes people who signed up this year, since their month and day match today but one year has not passed yet. Target data extension: Anniversary_Today, primary key SubscriberKey, Overwrite.

‍

Edge cases to check

Placeholder dates such as 1900-01-01 in the birthdate field will create false matches, so exclude them or clean the source.
Null and future dates should be filtered out before they reach the email.
The server date from GETDATE() is not necessarily your audience's local date, so decide which time zone defines "today" and test around midnight.
Duplicate contacts in the source will send two birthday emails, so deduplicate first. Our guide to deduplicating contacts in SFMC shows how.

‍

Automate the birthday audience in Automation Studio

‍

Quick answer: the setup checklist

Setting	Value
Source data extension	Customers_Master (with a Date field for BirthDate)
Target data extension	Birthday_Today, sendable, subscriber relationship on SubscriberKey
Primary key	SubscriberKey
Data action	Overwrite
Schedule	Daily, before your send window
Next step	Send activity or Journey Builder entry

Create the sendable target data extension with the four fields from the query.
Create a SQL Query activity, paste the leap day safe query, and pick the target with the Overwrite data action.
Create an automation with a schedule, and add the query as step one.
Add an email send activity as step two, or start a journey from the target data extension.
Run the automation once manually and check the row count before you activate the schedule.

If you are not sure which data action to pick, read our comparison of Overwrite, Update and Append. For birthdays, Overwrite is almost always right because the audience is only valid for one day.

‍

Feeding Journey Builder

If you prefer a journey over a direct send, use the target data extension as an entry source. The Salesforce help says the entry source must be a sendable data extension, and that an Automation Studio schedule can drive it when the automation includes a query activity that modifies that data extension.

Our guide to the Journey Builder entry source data extension covers the setup in more detail, including how to choose between evaluating all records or only new ones.

‍

FAQ

‍

How do I find birthdays today in SFMC SQL?

Compare the month and day of the birthdate field with the month and day of GETDATE() using DATEPART(). Do not compare full dates, because the birth year never matches the current year.

‍

How do I include February 29 birthdays?

Add a second condition that fires on February 28 in non-leap years and selects people born on February 29. Use the modulus operator to test whether the current year is a leap year.

‍

Should I use Overwrite or Append for a birthday data extension?

Use Overwrite. A birthday audience is only valid for the day it was built, so you want the target emptied and refilled on every run rather than growing with old rows.

‍

Can I send a birthday email a few days early?

Yes. Use DATEADD(day, 3, GETDATE()) in place of GETDATE() in the month and day conditions to find birthdays three days out. Mind the month end and leap day edge cases when you shift the date.

‍

Build this audience in seconds with QAiry

‍

Birthday and anniversary audiences are a good example of SQL that is simple in idea and fiddly in detail: the leap day rule, the null checks and the target settings. QAiry turns a plain-language request into production-ready SFMC SQL and Data Extensions, installed as an app in your own SFMC.

If you want to see it on your own data, install QAiry free or watch a product demo.

Related guides