kwizcom banner advertisement

SharePoint Online: CAML Query for Date Time Field in PowerShell

Requirement: SharePoint Online CAML Query to Filter by Date Time Field Values

SharePoint CAML: Order By Created Date
Here is the SharePoint Online CAML Query to order by using "created date" field
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
 
#Get Credentials to connect
$Cred= Get-Credential
  
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
               <OrderBy><FieldRef Name='Created' Ascending='FALSE' /></OrderBy> 
    </Query>
</View>"

#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

Write-host "Total Number of Items:"$ListItems.count

#Loop through each List Item
ForEach($Item in $ListItems)
{
    #Do something
    Write-host "Item: $($Item["Title"]) Created On: $($Item["Created"])"
}

CAML Query to Filter by Date Field:
CAML to filter by date, Say, Get Project List items where the "Project Start Date" is equal to: 20th June 2017
<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ProjectStartDate' /><Value Type='DateTime'>2017-06-20T12:00:00Z</Value>
            </Eq>
        </Where>
    </Query>
</View>
 
SharePoint Online: CAML Query with "Today" examples:
Get all projects where the project start date is today.
<View>
    <Query>
        <Where>
            <Gt>
                <FieldRef Name='ProjectStartDate' /><Value Type='DateTime'><Today /></Value>
            </Gt>
        </Where>
    </Query>
</View>
Here is an another example: Get All Projects where the project start date is within next 10 days from Today's date.
<View Scope='RecursiveAll'>
    <Query>        
        <Where>
                <Geq>
                    <FieldRef Name='ProjectStartDate' />
                         <Value Type='DateTime'><Today OffsetDays='10' /></Value>
                </Geq>
        </Where>
    </Query>
</View>"
You can also use the OffsetDays as "-10" to get the projects with start date is in the past 10 days.

CAML Query for Greater than Date
Or you can specify a particular date: Say, all dates after 1st Jan of 2017.
<Gt><FieldRef Name='ProjectStartDate' /><Value Type='DateTime'>2017-01-01T12:00:00Z</Value></Gt>

SharePoint CAML Query to Filter Between Dates
Say, we want to get all projects where the project start date is between 1st Jan 2017 to 31st Dec 2017. Here is the SharePoint CAML query for date range.
<View Scope='RecursiveAll'>
    <Query>        
        <Where>
            <And>
                <Geq>
                    <FieldRef Name='ProjectStartDate' />
                        <Value Type='DateTime'>2017-01-01T12:00:00Z</Value>
                </Geq>
                <Leq><FieldRef Name='ProjectStartDate' />
                    <Value Type='DateTime'>2017-12-31T12:00:00Z</Value>
                </Leq>
            </And>
        </Where>
    </Query>
</View>

SharePoint CAML Datetime to Include Time
Here is the PowerShell for SharePoint CAML Datetime field which includes time and filters:
<View Scope='RecursiveAll'>
    <Query>        
        <Where>
            <And>
                <Geq>
                    <FieldRef Name='ProjectStartDate' />
                    <Value Type='DateTime' IncludeTimeValue='TRUE'>2017-01-05T09:00:00Z</Value>
                </Geq>
                <Leq>
                    <FieldRef Name='ProjectStartDate' />
                    <Value Type='DateTime' IncludeTimeValue='TRUE'>2017-01-05T17:00:00Z</Value>
                </Leq>
            </And>
        </Where>
    </Query>
</View>

To convert the date time field values to CAML format, use:
[System.DateTime]::Now.ToString("yyyy-MM-ddTHH:mm:ssZ")
SharePoint Online: CAML Query for Date Time Field in PowerShell SharePoint Online: CAML Query for Date Time Field in PowerShell Reviewed by Salaudeen Rajack on June 05, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.