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

Attribution to: highfive

Possible Suggestion/Solution #1

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");

Attribution to: highfive
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/32150