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.
- Today
- Yesterday
- Last 7 days
- 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
Best Answer
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 withSOQL
.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.