0

I am trying to send a message to everyone whose birthdate is in two weeks but the date is of a different format and the query thus returns 0 rows.

The query I am currently using is the following

SELECT FirstName, PersonEmail AS EmailAddress, Id AS SubscriberKey, Language__c AS Language
FROM ent.[Account_Salesforce_1] 
WHERE PersonBirthdate = DATEADD(day, 14, cast(GetDate() AS date))
AND FirstName IS NOT NULL
AND PersonEmail IS NOT NULL
AND Id IS NOT NULL
AND Language__c IS NOT NULL

The format that has been passed from Service Cloud is the following: Thursday, December 13, 1984 12:00 AM

To what type do I need to change the format in order to make this work?

  • Can you remove the cast function and give it a try? If the PersonBirthdate is a date field in sync DE, it doesn't require CAST to compare. – Brad Sapkota Nov 30 '19 at 7:05
  • I just realized I am trying to compare a current date against a date from 1984... How can I focus on just the month and day? – user3485470 Nov 30 '19 at 12:34
1

Two remarks:

  • you also compare the year, which will never evaluate to true.
  • you don’t observe the system time zone (UTC-6) which in some cases can cause issues, as the date of birth is normally provided in local time zone

Here is a sample SQL overcoming both issues:

select * from anywhere 
WHERE 
datepart(day, [Date of birth]) = 
datepart(day,SYSDATETIMEOFFSET() 
AT TIME ZONE 'Central European Standard Time')
AND 
datepart(month, [Date of birth]) = 
datepart(month,SYSDATETIMEOFFSET() 
AT TIME ZONE 'Central European Standard Time’)
| improve this answer | |

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.