-1

I have two queries for identifying customers for promotion. The first target is composed of customers who did their last purchase more than 30 days from the current date and the WHERE clause works properly with the following structure:

WHERE GETDATE() - Order_Date__c >30

When I have to identify customers who did their last purchase exactly 30 days ago,changed the WHERE clause as follows

WHERE GETDATE() - Order_Date__c =30 

but it doesn't work as the result is the same as for >30 days.

What is wrong with the above?

2
  • Have you looked at using a date diff function
    – EazyE
    Commented Dec 30, 2020 at 13:36
  • @EazyE yours is the best answer
    – Lucia
    Commented Jan 1, 2021 at 16:55

2 Answers 2

1

Use DATEDIFF function:

WHERE DATEDIFF(DAY, Order_Date__c, GETDATE()) = 30

You can find more examples in my SFMC SQL docs.

-1

I would suggest going trough those pages and using APEX to help you here :

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_datetime.htm

First query:

Date dateForSoql = Today().addDays(-30);
WHERE :dateForSoql > Order_Date__c

OR

WHERE LAST_N_DAYS:30 > Order_Date__c

Second query:

Date dateForSoql = Today().addDays(-30);

WHERE :dateForSoql = Order_Date__c
4
  • This is marketing cloud und has nothing to do with apex or soql, therefore your answer does not provide a solution to the question. Commented Feb 18, 2021 at 8:24
  • The only place where it is mentioned that this is regarding marketing could is in the tags. Would you please update the title and the questio details. Commented Feb 19, 2021 at 9:23
  • Yes the tags are important: They show SQL, Marketing Cloud and Automation Studio. I think the title and details are sufficient in this case. I rarely edit cases, only if highlighting/markup is wrong or tags are missing. The rest should be done by the author. Commented Feb 19, 2021 at 10:31
  • Sorry I though you are actually the author. Commented Feb 19, 2021 at 11:37

You must log in to answer this question.

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