SharePoint Online: CAML Query for Date Time Fields in PowerShell

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

SharePoint CAML: Order By Created Date

Collaboration Application Markup Language (CAML) is an XML-based language used to define and query SharePoint data. In this article, we will explore how to use CAML to query the Date and Time fields in SharePoint.

Here is the SharePoint Online CAML Query to Order by using the “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 another example: Get All Projects, where the project start date is within the 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 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 the 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 – Range:

<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 the following:

[System.DateTime]::Now.ToString("yyyy-MM-ddTHH:mm:ssZ")

Last but not least: Use the CAML Query Builder Tools to generate the CAML Query for SharePoint Online.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

One thought on “SharePoint Online: CAML Query for Date Time Fields in PowerShell

  • Thank you very much for the content which helps me a lot and appreciate that very much.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *