0

I have an automation (running monthly) which adds records into a data extension if their birthday falls within the next month

Records are then added into a journey which uses the Birthdate date field from the data extension to send an email either 7 days or 14 days prior to the birthdate using the wait by attribute

As the birthdate is already in the past, the wait by attribute does not work, so is there a way to use a sql query in automation studio to take the birthdate field and update it back into the DE to have the same day and month but this current year

eg a birthdate of 01.01.1980 would be updated in the data base to 01.01.2020 (current year)

  • Hi ! are you looking only for the t-sql query piece of the solution? – Javier De Mauri Aug 13 at 15:01
1

In sacrifice of performance, I would recommend using sub queries, something like this below. But I would put in the journey all those whose birthday is in 14 days, with a 7 day wait for the first message.

SELECT ContactKey,
CASE 
    WHEN NextBirthday < getdate() THEN DateAdd(year, 1, NextBirthday)
    ELSE NextBirthday END as NextBirthday
FROM (
    SELECT ContactKey,
    DATEFROMPARTS(YEAR(getdate()), MONTH(birthday), DAY(birthday)) as NextBirthday
    FROM [Contacts]) tmp
| improve this answer | |
0

I believe you can use DateAdd() to do this.

Something like:

SELECT ContactKey,
dateadd(year, (year(getDate()) - year(birthday)), birthday) as birthday
FROM [myDE]

or you can build the date field with DateFromParts:

SELECT ContactKey,
DATEFROMPARTS(YEAR(GetDate()),MONTH(birthday),DAY(birthday)) as birthday
FROM [myDE]

To account for year transitions, you can use a CASE statement to move any birthdays that have already passed that year into the next year:

SELECT ContactKey,
CASE
  WHEN MONTH(EntryDate) < MONTH(GetDate()) OR (MONTH(EntryDate) = MONTH(GetDate()) AND DAY(EntryDate) < DAY(GetDate()))  THEN
    dateadd(year, (year(getDate())+1 - year(birthday)), birthday) as birthday
    /* OR DATEFROMPARTS(YEAR(GetDate())+1,MONTH(birthday),DAY(birthday)) as birthday */
  ELSE
    dateadd(year, (year(getDate()) - year(birthday)), birthday) as birthday
    /* OR DATEFROMPARTS(YEAR(GetDate()),MONTH(birthday),DAY(birthday)) as birthday */
END
FROM [myDE]

Just know that both are not super performant so with high volume, it may choke and time out.

| improve this answer | |
  • Solution 1: Does this work if runtime is e.g. at the end of december? I can't get it to work right now, but I believe if this runs e.g. on December 29th, and next anniversary is January 15th, it will not result in the correct date (January 15th, 2021) but January 15th 2020. I tested by replacing getDate() with Dateadd(dd,138,getdate()) to simulate runtime at the end of the year. It would output the next anniversary date in 2020, the current year. – Jonas Lamberty Aug 13 at 17:08
  • If you need to account for any rules like that, you can likely do a CASE statement around it to account for that. Will edit my answer to account for this. – Gortonington Aug 13 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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