Tag Archives: CAML

CAML Query did not return result for Date Range query

Having developing SharePoint for so many years, I just got to know that you CANNOT perform CAML Query for Date Range where your ISO Value is a DateTime.MinValue.

CAML Query does not support Date Value of DateTime.MinValue

SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.MinValue)

For example, if you were to query the Event Date Time from day 1

<Geq> 
 <FieldRef Name='EventDate' />
 <Value IncludeTimeValue='FALSE' Type = 'DateTime'>" + 
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.MinValue) + @" </Value>
 </Geq>

DO NOT DO this. You will never get any result. The minimal value that you can insert (had some googling around the web), is 1900/01/01.

Just make sure you conduct a date time in 1900 year onwards and you will be safe.

SPListItem.Delete Best Practise – SharePoint SPList

Today, My Client has a new requirement to delete certain records from a SharePoint Custom List which contains 80+ k of items. First thing came into my head is to run a powershell script to delete the item based on a certain filter condition. (In my case, it is to delete item with column “Application” equals to “Event”)

I drafted this powershell script and ran with Administrator rights.


$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$items = $list.Items | Where {$_["Application" ] -match "Event"}

foreach($item in $items)

{

Write-Host "Deleting " $item.ID

$item.Delete()

}

Well, with the huge number of list item in the list, one SPListItem took me 40+ second to delete! That’s ridiculous. And what if i have 10k record to be deleted? It would take up 400k seconds (111 hours) to complete!

After few minutes of googling, some suggested to use CAML Query and limit the number of query by using RowLimit = 1000

This doesn’t really help as i really want to delete them ALL at once!

The better way to delete the items is to change the way you query the data especially when comes to retrieving the List item. Here it goes


$web = Get-SPWeb <SharePoint URL>

$listName = "LIST NAME HERE"

$list = $web.Lists[$listName]

$query = New-Object Microsoft.SharePoint.SPQuery

$query.RowLimit = 999999

$query.Query = "<Where><Eq><FieldRef Name='Application'/><Value Type='Text'>Event</Value></Eq></Where>"

$items = $list.GetItems($query)

foreach($item in $items){

Write-Host "Deleting" $item.ID

$list.GetItemById($item.ID).Delete()

}

Woosh!!

the script completed in 30+ mins!