0

i am trying to filter with a SQL query a data extension. I tried to get the people in de DE which have their birthday today. The format of the birthday field is i think smalldatetime. This is how it looks like "4/6/1980 12:00:00 AM".

If i try something like:

select convert(date, contact.Birthdate) as Birthday
from Contact_Salesforce contact
where contact.Birthdate = GetDate()

It provides 0 results because the contact.Birthdate outputs smalldatetime as a value like this: "4/6/1980 12:00:00 AM". Can someone help me with this?

5
  • where is your FROM clause? Apr 24, 2018 at 11:15
  • Oops.. forgot to put that in the question. Question updated now. Apr 24, 2018 at 11:20
  • can you explain "won't work" - does it error, provide 0 results, provide incorrect results, etc? Apr 24, 2018 at 12:08
  • It provides 0 results because the contact.Birthdate outputs smalldatetime as a value like this: "4/6/1980 12:00:00 AM". Apr 24, 2018 at 12:21
  • 2
    I don't believe you would want to include the year in your comparison either, unless you are expecting people that were literally born today. Apr 24, 2018 at 15:23

1 Answer 1

1

To make a comparison on equally formatted values, try something like this:

select convert(date, contact.Birthdate) as Birthday
from Contact_Salesforce contact
where CONVERT(date, contact.Birthdate) = CONVERT(date, GetDate())

This should match them on the MM/dd/yyyy format (e.g. 04/24/2018) instead of potentially comparing apples to oranges due to format differences.


EDIT - As @AnonWonderer stated, I believe it is the year that is throwing it off.

Try below instead:

select convert(date, contact.Birthdate) as Birthday
from Contact_Salesforce contact
where DATEPART("month", contact.Birthdate) = DATEPART("month", GetDate())
AND DATEPART("day", contact.Birthdate) = DATEPART("day", GetDate())
4
  • Thanks for you help Gortonington. I get the following error using your code: An error occurred while checking the query syntax. Errors: 'FORMAT' is not a recognized built-in function name. Apr 24, 2018 at 13:25
  • I apologize, I updated the answer and it should now fit in SFMC restrictions. Apr 24, 2018 at 13:41
  • It still returns 0 results. Apr 24, 2018 at 14:13
  • Your updated answer did the job Gortonington! Thanks for your help! Apr 25, 2018 at 6:31

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.