Adobe Campaign Standard Query for Birthday

Avatar

Avatar

Clarence_Bunch_

Avatar

Clarence_Bunch_

Clarence_Bunch_

15-06-2018

Floretlb,

Can you help me with a query to get the current date to send a Birthday email?  I am trying to using the Profile Attribute, Advanced Mode, and creating an expression (using @birthDate = GetDate()) but this is not returning any value.  Can you help?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

MarcelSzimonisz

MVP

Avatar

MarcelSzimonisz

MVP

MarcelSzimonisz
MVP

17-07-2018

Hello,

Yes forgot about this little thing.

So In this case I would create, before the birthday check, a new field in Enrichemnt activity  bday_currentYear and checked that date as I described previously. You can create this  field by using functions e.g

ToDate(Year(GetDate()) +'-'+Month(bday)+'-'+Day(bday))

Marcel

Answers (7)

Answers (7)

Avatar

Avatar

sqwgglz

Avatar

sqwgglz

sqwgglz

13-07-2018

Hi Marcel,

Doing it your doesn't take the year into consideration.

You say birthday (i.e.DD/MM) but it's actually the date of birth (i.e. DD/MM/YYYY) so saying X date is equal to date of birth would almost always pull incorrect information (as it's very unlikely a recipient will have a date of birth in 2018!)

Avatar

Avatar

MarcelSzimonisz

MVP

Avatar

MarcelSzimonisz

MVP

MarcelSzimonisz
MVP

11-07-2018

What about make it simply:

DateOnly(@bday) == DateOnly(current day)

or with lookahead

DateOnly(@bday) == DateOnly(current day +xyz days)

Marcel

Avatar

Avatar

alistairk161838

Avatar

alistairk161838

alistairk161838

26-06-2018

Hi Clarence,

The below should help with the leap year issue

Sending a recurring birthday email

Avatar

Avatar

Vapsy

Employee

Avatar

Vapsy

Employee

Vapsy
Employee

17-06-2018

The query filtering criteria provided by David will work in 99% cases. It will not send birthday emails to recipients born on 29th Feb and if the current year is not a leap year.

So such people should get an email on 1st of Mar or 28th of Feb, Please enhance the logic accordingly.

Regards,
vipul

Avatar

Avatar

davidk21713691

Avatar

davidk21713691

davidk21713691

15-06-2018

You need to write your query as

Month(@birthDate) = Month(GetDate()) and Day(@birthDate)=Day(GetDate())

Thanks

David

Avatar

Avatar

sqwgglz

Avatar

sqwgglz

sqwgglz

11-07-2018

Hey, I know this is a little old but It might be better to look for people who have a birthday coming up instead by using the AddDays function. For birthdays in 2 days for instance, use:

Month(@birthDate) = Month(AddDays(GetDate(), 2) and Day(@birthDate)=Day(AddDays(GetDate(), 2)

Avatar

Avatar

Clarence_Bunch_

Avatar

Clarence_Bunch_

Clarence_Bunch_

18-06-2018

Vipul,

How do you enhance the logic to accommodate these anomolies?