4

I'm working on a ASP.NET application which is calling our salesforce org via REST API. There is a feature to search data based on time period(DateTime field) as follows.

  1. Today
  2. Yesterday
  3. Last 7 days
  4. Custom selection (this is where I'm struggling)

Previously all filters were implemented by creating appropriate DateTime instance in .NET end and passing it to the query string. But the Time Zone difference is impacting the search results which I can understand.
Then first three filter criteria were implemented with SOQL Date Literals and looks working fine. But for the last option I should take user input and create the fromDate and toDate in C#, then pass into my query string.

"?q=SELECT my_fields FROM my_Object__c WHERE Date_Registered__c > " + DateTime.Parse(txtFromDate.Text.Substring(6, 4) + "/" + txtFromDate.Text.Substring(3, 2) + "/" + txtFromDate.Text.Substring(0, 2)).ToString("yyyy-MM-ddTHH:mm:ss.000Z") + " and Date_Registered__c < " + DateTime.Parse(txtToDate.Text.Substring(6, 4) + "/" + txtToDate.Text.Substring(3, 2) + "/" + txtToDate.Text.Substring(0, 2)).AddDays(1).ToString("yyyy-MM-ddTHH:mm:ss.000Z");

In this way I can't handle the time zone impact on my search results. So it's giving some unexpected results. There are so many examples to handle these when using Apex. But here it's for .NET and I found nothing useful.

Any guidance appreciate.
Thanks

3
  • FYI there is this convertTimezone mechanism (salesforce.com/us/developer/docs/soql_sosl/Content/…) that may be of some help.
    – Keith C
    Apr 8, 2014 at 10:04
  • Thanks @KeithC. I was thinking how to use convertTimezone here if there is a way. But still no luck. Can't think of a way to handle DAY_IN_MONTH(convertTimeZone(Date_Registered__c)) >25 and DAY_IN_MONTH(convertTimeZone(Date_Registered__c)) < 02 like scenario when the fromDate and toDate are from different calendar months. I tried applying CALENDAR_MONTH(),CALENDAR_YEAR(),DAY_IN_MONTH() altogether.
    – highfive
    Apr 8, 2014 at 12:09
  • Yes I couldn't think of how to use it; just wanted to make sure you knew about it given the problem is time zone related.
    – Keith C
    Apr 8, 2014 at 12:12

1 Answer 1

4

DAY_ONLY() Date Function resolved the headache. Since it's no need of filter based on the time, we can add the range by taking only the day, if it's possible with SOQL. DAY_ONLY() is giving that as follows.

DAY_ONLY(convertTimeZone(Date_Registered__c)) > 2013-11-01T00:00:00.000Z

So below query is working fine for such situations.

"?q=SELECT my_fields FROM my_Object__c WHERE DAY_ONLY(convertTimeZone(Date_Registered__c)) > " + DateTime.Parse(txtFromDate.Text.Substring(6, 4) + "/" + txtFromDate.Text.Substring(3, 2) + "/" + txtFromDate.Text.Substring(0, 2)).ToString("yyyy-MM-ddTHH:mm:ss.000Z") + " and DAY_ONLY(convertTimeZone(Date_Registered__c)) < " + DateTime.Parse(txtToDate.Text.Substring(6, 4) + "/" + txtToDate.Text.Substring(3, 2) + "/" + txtToDate.Text.Substring(0, 2)).AddDays(1).ToString("yyyy-MM-ddTHH:mm:ss.000Z");

You must log in to answer this question.

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