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.
    – eazy4me
    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".
    – eazy4me
    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.
    – eazy4me
    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.
    – eazy4me
    Apr 24, 2018 at 14:13
  • Your updated answer did the job Gortonington! Thanks for your help!
    – eazy4me
    Apr 25, 2018 at 6:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .