Adobe Campaign Standard Query for Birthday | Community
Skip to main content
C
Clarence_Bunch_
Level 4
June 15, 2018
Solved

Adobe Campaign Standard Query for Birthday

  • June 15, 2018
  • 8 replies
  • 7851 views

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?

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Marcel_Szimonisz

    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

    8 replies

    davidk21713691
    davidk21713691
    Level 3
    June 15, 2018

    You need to write your query as

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

    Thanks

    David

      vraghav
      Adobe Employee
      vraghavAdobe Employee
      Adobe Employee
      June 18, 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

        C
        Clarence_Bunch_Author
        Level 4
        June 18, 2018

        Vipul,

        How do you enhance the logic to accommodate these anomolies?

        A
        alistairk161838
        Level 3
        June 26, 2018

        Hi Clarence,

        The below should help with the leap year issue

        Sending a recurring birthday email

          sqwgglz
          sqwgglz
          Level 3
          July 11, 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)

          Marcel_Szimonisz
          Community Advisor
          Marcel_SzimoniszCommunity Advisor
          Community Advisor
          July 11, 2018

          What about make it simply:

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

          or with lookahead

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

          Marcel

            sqwgglz
            sqwgglz
            Level 3
            July 13, 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!)

              Marcel_Szimonisz
              Community Advisor
              Marcel_SzimoniszCommunity AdvisorAccepted solution
              Community Advisor
              July 17, 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

                Terms & ConditionsAccessibility statement

                Loading footer...