2

First, some setup: My Site is set to Eastern Time, and the server is located in Eastern Time.

I'm trying to run a query against a list using the created date. I have tried using all variations I can think of and just can't get the results I want. My CAML query has a date specified as 2012-08-25T06:01:49Z which is in ISO8601 format. It is a UTC date. But when I execute my query, it goes to the database as 2012-08-25 06:01:49. So, this works as expected and I get the results I want. Now, if I change my user regional settings to Pacific. The same query sends to the database with 2012-08-25 09:01:49 and I don't get the results I expect.

Since I provided the date in the CAML as UTC, is there any way to get the query to execute with the exact date I passed in instead of translating it using Regional settings? I've torn my hair out enough over this and was hoping someone might have some insight?

Improve this question

2 Answers 2

Reset to default
1

Find here (http://sladescross.wordpress.com/2012/05/28/spquery-iso-datetime/) an explanation on how the ISODate gets stored and used. In simple words DateTime values as stored as UTC and used as such, except for when column names are obtained via an Indexer, which reads date-time values according to Regional Settings for the site.

Beyond, if you do code try always returning to ISO DateTime as such SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow));

See here for more conversion exampls http://prasanjitmandal.blogspot.ch/2010/06/sharepoint-datetime-format-conversions.html

Improve this answer
1
  • Thanks for the reply and information. My biggest problem isn't getting things into or out of UTC. The problem is when I provide a UTC date to an SPQuery, it sends that date to the DB as the users local time instead of keeping it as UTC. Since I'm doing stuff that depends highly on date/time, this is causing results to be left off that should be included.
    – TehOne
    Commented Aug 27, 2012 at 17:24
1

Ok, so I figured this out (Edit: No I Didn't). It turns out, that there is a StorageTZ attribute on the Field element for CAML. You can set this value to UTC and then it will use the date as you pass it in instead of converting it to the users local time.

For the Field element: http://msdn.microsoft.com/en-us/library/dd588183(v=office.11).aspx

And this is where I first saw the StorageTZ attribute: http://msdn.microsoft.com/en-us/library/ms197282.aspx (found this link via the http://sladescross.wordpress.com/2012/05/28/spquery-iso-datetime/ link that @C. Marius provided, so thank you!)

Improve this answer
2
  • 1
    Ok, I may have spoken too soon. Even with this option the query is still sent to the DB with an automatic adjustment based on my regional settings... Oh SharePoint, how I hate thee sometimes.
    – TehOne
    Commented Aug 27, 2012 at 18:44
  • If you solved the problem stated in your original question, you should mark this answer as accepted answer to help future visitors.
    – Benny Skogberg ♦
    Commented Apr 1, 2014 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.