SharePoint internally stores dates for list items as UTC dates. While that’s good for doing date comparisons because it takes time zones out of the equation, it can introduce a little head-scratching when it comes time to write the CAML code to do the comparison.

The key is the StorageTZ attribute as shown in this CAML snippet:

<Where>
  <Gt>
    <FieldRef Name='Created' />
    <Value Type='DateTime' IncludeTimeValue='TRUE' StorageTZ='TRUE'>
      2012-10-24T21:30:46Z
    </Value>
  </Gt>
</Where>

Setting the StorageTZ attribute to TRUE tells SharePoint to do the comparison using the native storage time zone (UTC time) of the field value. In this particular snippet I’ve also set the IncludeTimeValue attribute to TRUE to tell SharePoint I want to use the date and time components of the field value in the comparison (by default only the date component is used).

And if you’re wondering about the value I’m using for the comparison (2012-10-24T21:30:46Z), it’s in the ISO 8601 format that SharePoint expects for date values in CAML queries. You can create a date string in this format from any .NET DateTime object by calling SPUtility.CreateISO8601DateTimeFromSystemDateTime() in the Microsoft.SharePoint.Utilities namespace.

If you do use that function, however, just be warned that it doesn’t contain as much logic as you might expect. For example, you might expect it to look at your DateTime object and determine whether it’s in UTC or local time before generating the ISO 8601 date string. It doesn’t, however. It simply takes the year, month, day, hour, minute, and second components of the DateTime object and returns them as a string with a ‘T’ and a ‘Z’ thrown in. So it’s up to you to make sure your DateTime object has been converted to UTC before passing it to that function.