[SalesForce] How to do a SOQL Query of DAY_ONLY on a DateTime field

Here is the problem. We use Stripe to process credit card payments, and Stripe deposits the money into our account two days later, minus fees. I've created a schedulable class that runs every morning, and creates the deposits and fee payments automatically in our SalesForce Accounting Application. This way, our org sees the money appear in real time in our accounting software at the same time we get the deposit from Stripe. However, some of the payments are not pulled in the query, so I think that we have a problem with timezone conversion. We are in NYC, GMT-5, and payments after 7pm EST are not pulled.

Here is my Query:

//Query Payments with a date of today – 2 days
date paymentdate = date.today().addDays(-2);

    List <Payment__c> payments = [SELECT name, id, Amount__c, Payment_Date__c FROM  Payment__c WHERE DAY_ONLY(convertTimezone(Payment_Date__c)) = :paymentdate AND Payment_Processor__c = 'Stripe'];

In this case paymentdate is a Date field, which should be in EST, and Payment_Date__c is a DateTime field, which is a timestamp for the payment's receipt, should be in GMT. I've seen the posting here: Using Date in SOQL to get only records created on a certain date which addresses this issue, however my code is written like the solution, so I'm not sure how to fix this. Any advice would be appreciated.

Best Answer

I second opinion expressed by NSJonas above

Paymentdate will be in EST, provided user who has scheduled the job is also in EST time zone. Please verify that first. Seems like GMT date is converted into EST but is compared with some date in some other timeZone

I did a simple test : created a new scheduled Job with just debugging statements like this :

global class DateTimeZoneCheck implements Schedulable {
   global void execute(SchedulableContext SC) {
    system.debug(date.today());
    system.debug(system.now());

   }
}

Date.today() was printed in debug log , in time zone of user who has scheduled the job while System. now was in GMT like this :

enter image description here