Question
How to Retrieve Records in SFMC SQL Where a Date Field Equals 90 Days from Today?
I’m facing issues with an SQL query in Salesforce Marketing Cloud (SFMC) to retrieve records where the date field (Check_In_Date__c) equals exactly 90 days from today. I do not want to retrieve records between today and the date field—just those that match the 90-day condition.
Here’s the query I’ve tried:
WHERE o.Check_In_Date__c >= GETDATE()
AND o.Check_In_Date__c = DATEADD(day, 90, GETDATE())It doesn’t seem to work with the = operator, though other operators like >= and <= work. I also attempted the following, but they didn’t yield the expected results:
WHERE DATEDIFF(DAY, o.Check_In_Date__c, GETDATE()) = 90WHERE o.Check_In_Date__c = DATEADD(day, 90, GETDATE())Could someone help identify what I’m missing here and provide the correct SQL query for this use case?
Keywords: SFMC, SQL, date comparison, 90 days from today, Check_In_Date__c, Marketing Cloud.
Answer
In Salesforce Marketing Cloud (SFMC), you might face a situation where you need to query records where a specific date field, such as Check_In_Date__c, is exactly 90 days from today. A common problem arises when using SQL functions like GETDATE() because it includes both date and time components, which may cause mismatches if the time parts differ.
Here’s a detailed approach to solve this problem:
Correct Query Using CAST
The issue likely lies in the time component included in the GETDATE() function. To resolve this, you can cast both the date field and the calculated date to exclude the time part. Use the following query:
sqlCopy codeWHERE CAST(o.Check_In_Date__c AS DATE) = CAST(DATEADD(day, 90, GETDATE()) AS DATE)This query ensures that only the date components are compared, making it accurate for scenarios where time discrepancies might otherwise cause issues.
Alternative Query Using DATEDIFF
If you prefer a different method, you can use the DATEDIFF function to calculate the difference in days between Check_In_Date__c and today’s date. The query would look like this:
<code>sqlCopy code<code>WHERE DATEDIFF(DAY, GETDATE(), o.Check_In_Date__c) = 90</code></code>This query calculates the exact number of days between GETDATE() and the Check_In_Date__c field. If the difference is 90, it retrieves the record.
Explanation of Key Functions
GETDATE(): Returns the current date and time.CAST(): Converts a value to a specific data type, in this case,DATE, to strip off the time part.DATEADD(): Adds a specific number of days to a date.DATEDIFF(): Calculates the difference between two dates based on the specified unit, here,DAY.
By using these queries, you can ensure precise filtering of records that are exactly 90 days from today. Choose the approach that best fits your database design and requirements.
Job-Oriented Salesforce Training with 100% Money Back Guarantee
Our Salesforce training in Mumbai offers an immersive experience into the Salesforce platform, equipping you with the necessary skills to succeed in the CRM industry. The curriculum includes in-depth coverage of key areas such as Salesforce Admin, Developer, and AI, blending theoretical knowledge with hands-on practice. Through live projects and assignments, you’ll gain practical experience that will enable you to tackle complex business problems with Salesforce solutions. Our experienced instructors ensure you develop the technical expertise and industry insights needed to thrive in the Salesforce ecosystem.
Beyond technical skills, our Salesforce training in Mumbai also offers personalized mentorship, guidance for exam preparation, and interview coaching to give you a competitive edge in the job market. You’ll have access to comprehensive study materials, real-world project exposure, and dedicated support throughout your learning journey. By the end of the course, you’ll not only be ready for certification exams but also armed with practical experience and problem-solving abilities that employers highly value. Kickstart your Salesforce career with us and open doors to a range of career opportunities!
