Expand my Community achievements bar.

SOLVED

Adobe Campaign Standard Query for Birthday

Avatar

Level 4

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?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

8 Replies

Avatar

Level 3

You need to write your query as

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

Thanks

David

Avatar

Employee

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

Level 4

Vipul,

How do you enhance the logic to accommodate these anomolies?

Avatar

Level 3

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

Community Advisor

What about make it simply:

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

or with lookahead

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

Marcel

Avatar

Level 3

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

Correct answer by
Community Advisor

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