SQL - GETDATE() not working

Question

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?

Answer 1

Use DATEDIFF function:

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

You can find more examples in my SFMC SQL docs.

Answer 2

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