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?

  • where is your FROM clause? – garek007 Apr 24 '18 at 11:15
  • Oops.. forgot to put that in the question. Question updated now. – wfrankhuizen Apr 24 '18 at 11:20
  • can you explain "won't work" - does it error, provide 0 results, provide incorrect results, etc? – Gortonington Apr 24 '18 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". – wfrankhuizen Apr 24 '18 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. – AnonWonderer Apr 24 '18 at 15:23
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())
  • 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. – wfrankhuizen Apr 24 '18 at 13:25
  • I apologize, I updated the answer and it should now fit in SFMC restrictions. – Gortonington Apr 24 '18 at 13:41
  • It still returns 0 results. – wfrankhuizen Apr 24 '18 at 14:13
  • Your updated answer did the job Gortonington! Thanks for your help! – wfrankhuizen Apr 25 '18 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.