1

I'm trying to pull birthdays that are coming up in 1 day.

Select ID,
Email,
Birthdate
from Contact_Salesforce contact
Where Birthdate = DATEADD(DAY, 1, GETDATE())

I receive 0 results but I know there are subscribers that meet the criteria.

Also tried

select ID,
Email,
Birthdate
from Contact_Salesforce contact
Where Birthdate = DATEADD(DAY, 1, CAST(GETDATE() AS date))

0 results again. What am I missing? Thanks for your help.

1
  • Does birthdate have a time stamp and/or year? – EazyE Aug 23 '20 at 18:26
3

You're basically asking for all contacts that WILL be born tomorrow. Compare Month and Day DateParts of the Birthdate and tomorrow's date to isolate those that WERE born on the same day and month as that of tomorrow:

SELECT
    SubscriberKey,
    Birthdate
FROM
    Contact_Salesforce
WHERE
    DATEPART(day,Birthdate) = DATEPART(day,DATEADD(day,1,GETDATE())) AND
    DATEPART(month,Birthdate) = DATEPART(month,DATEADD(day,1,GETDATE()))

Note that the above is a bit simplistic. Check how those Birthdates are being stored and converted when synced to Data Extensions. To account for timezone differences, you might want to modify the above or choose carefully at which point in the day you want to run your query in an Automation.

-1
SELECT SubscriberKey
, Birthdate
, ..yourfields..
FROM [yourDE]
WHERE CONVERT(date, Birthdate) = CONVERT(date, DATEADD(day,+10, GETDATE()))
/* Target: yourBdayDE */
/* Action: Overwrite */

This query will pull those who have a birthday exactly 10 days in the future. Please notice I used CONVERT() to change the fields from DATETIME to just Date. This way it will only match the day, so the time field will not mess with your results. Also, note that I have it with the action of 'Overwrite'.

An Additional Suggestion is also mentioned here,

Send an email at 14 days before birthday

You can go through it too.

2
  • 2
    Will this not look for people born 10 days from today? – Lukas Lunow Aug 24 '20 at 6:55
  • 1
    Most of the birthdate queries I've seen isolate the month and day as @Macca illustrated. – Adam Spriggs Aug 24 '20 at 13:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.