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

         

             

Stored Procedure for Add Master and Detail

https://www.blogger.com/blogger.g?blogID=2984821582526309017#editor/target=post;postID=1068201323384154429

Convert a delimited string to a table in SQL

SQL Server
Convert a delimited string to a table using  Recursive Common Table Expression

declare @string as varchar(200),@start as int, @finish as int
set @string='Sinthiya,I,,Love,You,Very,Much,'+','

with cte(start,finish) as
(
  select 1 as start,CHARINDEX(',',@string) as finish
  union all 
  select cte.finish+1 as start, CHARINDEX(',',@string,cte.finish+1) as finish 
  from cte where cte.finish <>0
)
select  SUBSTRING(@string,cte.start,cte.finish-cte.start) from cte where cte.finish<>0

Online Editor

Let's say you're away from your computer and all you've got is a web browser. You'd still like to run a piece of code (e.g. to check an answer on SO). What are your options?
Let's create a list of on-line interpreters of various programming languages. Here are some examples:

• Haskell: http://tryhaskell.org/
• Scala: http://www.simplyscala.com/
• Many languages: http://ideone.com/
• Many languages: http://codepad.org/

JavaScript Intellisense for SharePoint 2010 Client Object Model in Script .js file

/// <reference path ="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.9.0.js" />

/// <reference name="MicrosoftAjax.js" />

/// <reference path="file://C:/Program Files/Common Files/Microsoft Shared/Web Server Extensions/14/TEMPLATE/LAYOUTS/SP.core.debug.js" />

/// <reference path="file://C:/Program Files/Common Files/Microsoft Shared/Web Server Extensions/14/TEMPLATE/LAYOUTS/SP.debug.js" />

JavaScript Intellisense for SharePoint 2010 Client Object Model in Application Page .aspx file

<asp:Content ID="PageHead" ContentPlaceHolderID="PlaceHolderAdditionalPageHead" runat="server">
    <SharePoint:ScriptLink ID="ScriptLinkB" runat="server" Name="sp.js">
    </SharePoint:ScriptLink>
    <script type="text/javascript" src="/dev/_layouts/SP.debug.js" />
    <script type="text/javascript">
        var ctx = SP.ClientContext.get_current();
        ctx.get_serverVersion();
        
    </script>
</asp:Content>






JavaScript Intellisense for SharePoint 2010 Client Object Model in Visual WebPart .aspx file






<% #if XXXX %>
<script type="text/javascript" src="/dev/_layouts/MicrosoftAjax.js" />
<script type="text/javascript" src="/dev/_layouts/SP.debug.js" />
<% #endif %>
    <script type="text/ecmascript" language="ecmascript">
 
        var x = SP.ClientContext.get_current();
        
    </script>

JavaScript Intellisense for SharePoint 2010 Client Object Model in Visual WebPart (SandBox) .aspx file

<% #if XXXX %>
<script type="text/javascript" src="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\MicrosoftAjax.js" />
<script type="text/javascript" src="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\SP.debug.js" />
<% #endif %>
 
 <script type="text/ecmascript" language="ecmascript">
 
     var x = SP.ClientContext.get_current();
        
 </script>