[SalesForce] Marketing Cloud SQL Birthday date (smalldatetime) is equal to today

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?

Best Answer

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())

Related Solutions

Marketing Cloud – API – Data Extension – Filter by Date

You were very close, you need to put it in the YYYY-MM-DDTHH:mm:ss format, like you had in your second example. The difference though is that your equals is so specific it likely was not accounting for milliseconds, etc. that would be inside the data but not displayed. Returning a false result.

So you would pass something like:

?$filter=date gte '2020-01-13T00%3a00%3a00'

To get all the entries that have a value in the date field that are before January 13th 2020. I usually use it as a 24 hour clock for the time portion and that seems to work well.

You also can do it without a time (defaults to 00:00:00):

?$filter=entrydate gte '2020-01-13'

I would not recommend eq for dates though, but instead set a 'between' statement. This is because what is stored may have more added to the seconds like milliseconds, etc. that we cannot see or account for and will return false.

Something like this is my recommendation:

?$filter=date gte '2020-01-13T13%3a22%3a43' and date lt '2020-01-13T13%3a22%3a44'

Which would get those that exist within a 1 second timeline up to the next second.

Best Answer

Related Solutions

Marketing Cloud – API – Data Extension – Filter by Date

Related Topic