[SalesForce] Why is Task ActivityDate (Due Date) different when queried using SOQL

When I query a Task's ActivityDate in Apex accessed using JavaScript Remoting, I receive a date that is different from the date displayed in the Salesforce interface.

The query below (with a generic Account Id) results in 2012-09-11 for a specific Task, but the date is displayed as 9/12/2012 when viewing the Task in the Salesforce interface. It seems all ActivityDate fields for all Tasks are different by a day.

SELECT Id, AccountId, Account.Name, Priority, Status, Subject, ActivityDate, Type, 
Owner.Name, Owner.Id, What.Name, WhatId, WhoId, Who.Name, LastModifiedDate FROM Task 
WHERE AccountId IN ('001000000000000')

The time-zone of the User accessing Salesforce through the interface and through SOQL is the same – (GMT-05:00) Central Daylight Time (America/Chicago).

Does anyone have advice on how I can account for this in Apex or JavaScript (without Visualforce; I'm using Remote Actions)?

Best Answer

Javascript remoting returns dates in milliseconds since epoch. The javascript date constructor interprets the milliseconds since epoch as a date time in your local time zone, thus the discrepancy. Use the following function to correct the date:

var ONE_MINUTE = 60000;
...
function epochToDate(jsRemotingResult) {
  var epochDate = new Date(jsRemotingResult);
  return new Date(epochDate.getTime() + epochDate.getTimezoneOffset() * ONE_MINUTE);
}

Related Solutions

[SalesForce] SOQL Date Literal “TODAY” for different geographical regions

I managed to get something working that might solve your problem.

Select id from Asset where DAY_ONLY(InstallDate) = :DateTime.now().addHours(<x>).date()

Where <x> is the difference in hours between the time zone of the user that schedules/runs your batch job, and the time zone of your target region

From the documentation on SOQL date functions, it says that you can use a SOQL date function on a field in the WHERE clause of the query without needing a GROUP BY clause.

It goes on to say that you can't use a SOQL date literal (such as TODAY), to compare against, but it doesn't say anything about not being able to bind an apex expression to compare against.

Hard-coding the timezone difference in apex doesn't sit too well with me. You could substitute hard-coding with using a custom setting in your org. A custom metadata type would probably work as well.

[SalesForce] Date formatting when using JavaScript Remoting

You could either use some of the Javascript date format functions (i.e.)

var date = new Date(1324339200000);

date.toString("MMM dd"); // "Dec 20"

Change the return type of your remote action:

@RemoteAction
global static String[] GetData(string someParam)
{
    String[] results = New String[]{};
    for(MyCustObj__c o : [Select Id, Name, Date__c from MyCustObj__c where myField__c = :someParam]){
        if(o.Date__c != null) results.add(o.Date__c.format());
    }

    return results;
}

Best Answer

Related Solutions

[SalesForce] SOQL Date Literal “TODAY” for different geographical regions

[SalesForce] Date formatting when using JavaScript Remoting

Related Topic