SharePoint: Query List within Time interval

SharePoint saves the time in current time zone it is specified in the regional setting!

When select a list item within specified time from a client machine using "Client Side Object Model"(CSOM), need to create the Datetime object in that (server)time zone. But C# creates the DateTime object with client Time zone

1. Get the server time zone

ClientContext context = new ClientContext(SiteUrl);

context.Credentials = new NetworkCredential("Administrator", "******", "DCE.local");

//context.Credentials = CredentialCache.DefaultCredentials;

//Get the server time zone

Microsoft.SharePoint.Client.TimeZone spTimeZone = context.Web.RegionalSettings.TimeZone;

context.Load(spTimeZone);

context.ExecuteQuery();

string fixedTimeZoneName = spTimeZone.Description.Replace("and", "&");

TimeZoneInfo serverZoneInfo = TimeZoneInfo.GetSystemTimeZones().FirstOrDefault(tz => tz.DisplayName == fixedTimeZoneName);

//var localTimeZone = TimeZoneInfo.Local;

2. Set time as midnight in server time zone

DateTimeOffset dtStartDateOff = new DateTimeOffset(DateTime.Parse(dtStartDate.ToShortDateString()),

                     +serverZoneInfo.GetUtcOffset(dtStartDate) ).ToUniversalTime();

DateTimeOffset dtEndDateOff = new DateTimeOffset(DateTime.Parse(dtEndDate.ToShortDateString()), 

                    +serverZoneInfo.GetUtcOffset(dtEndDate)).ToUniversalTime();

3. Create the CAML time with IncludeTimeValue='TRUE' StorageTZ='TRUE'

CamlQuery camlQuery = new CamlQuery();

string camlQueryString = @"<View><Query><Where><And> <Eq><FieldRef Name='Location' /><Value Type='Lookup'>{0}</Value></Eq> <And><Geq><FieldRef Name='PricingDate' /><Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE' >{1}</Value></Geq> <Leq><FieldRef Name='PricingDate' /><Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE'>{2}</Value></Leq> </And></And></Where> <OrderBy><FieldRef Name='PricingDate' Ascending='True' /></OrderBy></Query></View>";

string startDateFx = dtStartDateOff.ToString("yyyy-MM-ddTHH:mm:ssZ");

string endDatFx = dtEndDateOff.ToString("yyyy-MM-ddTHH:mm:ssZ");

camlQuery.ViewXml = string.Format(camlQueryString, location, startDateFx, endDatFx);

4. Execute CSOM

Web web = context.Web;

ListCollection lists = web.Lists;

List list = lists.GetByTitle(listName);     

ListItemCollection listItems = list.GetItems(camlQuery);

context.Load<ListItemCollection>(listItems);

context.ExecuteQuery();

int c = listItems.Count();

var priceListItems = listItems.ToList();

5. Return Date time will represent in client time zone. Adjust to show it server time-zone

var priceList2 = priceListItems.Select(i => new FuelPriceItem { 

PricingDate = DateTime.Parse(i["PricingDate"].ToString()), 

FuelPrice = double.Parse(i["FuelPrice"].ToString()) }).ToList();
          var priceList = priceList2.Select(i => new FuelPriceItem { 

PricingDate = TimeZoneInfo.ConvertTimeFromUtc(i.PricingDate, serverZoneInfo), 

FuelPrice = i.FuelPrice }).ToList();