Dates and times in Properties vs Fields

Question

I'm trying to roll my own archiver for SharePoint. I always try to let the underlying object serialize itself. Only when it fails do I try to use .ToString(). Anyway, while inspecting the Properties that are coming from an SPFile, and the Fields from the SPFile.Item.Fields, I'm running across some date/time properties that seem to be stored as strings like "0x01ce30e0|0x7ee3f800" in the Properties collection, but as DateTime's like "2013-04-03T23:00:00" in the Fields collection.

(To make things even more interesting, it looks like if SharePoint is storing a date, rather than a date/time, then it will use something like "2013-04-03T23:00:00".)

Anyway, my questions are: - Should I try to translate the the pair of hex numbers into a a DateTime, or just leave it alone? - If there is a conflict between a Field and a Property, which should I consider authoritative?

Answer 1

Have you seen this answer?

It's newer Office files that appear to be using this format. SharePoint is quite happy reading and writing them to/from this format, however you'll need to do a bit of work if you want to convert the hex to a DateTime value.

The hex value represents the number of ticks since 1st Jan 1600, so you may have found that your dates were looking like they were in the year 414 or something?

Anyway, to convert the hex to a DateTime you can use the following.

//fieldValue looks like "0x01cf2f8e|0x6e559e00"
string fieldValue = listItem.Properties[dateFieldName]
string hexValue = fieldValue.Replace("|", "").Replace("0x", "");
long ticks = long.Parse(hexvalue, NumberStyles.HexNumber);

DateTime date1 = DateTime.FromBinary(ticks).AddYears(1600);

You should (hopefully!) find that the converted hex value matches your UTC date time property.

i.e.

// UTC format like 2014-02-22T 05:25:00Z 
string utcFieldValue = listItem[dateField].ToString(); 
DateTime date2 = DateTime.Parse(utcFieldValue);

//date1 from above    
Assert.AreEqual(date1, date2);

Given that the values from the SPListItem.Properties Hashtable can take a couple of different formats, I'd stick with the indexer, i.e. listItem[fieldName] as this seems to consistently spit them out in a friendly format.

Answer 2

To my knowledge dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Time also as part of a date value is also represented as decimal number (for example, 12:00 PM is represented as 0.5 because it is half a day).

An example (source MSDN) This date 2006/12/1 12:45:25is actual the Serial number 39,052.5315393519.

I believe the HEXA is just another way of storing these serial numbers, but I cannot 100% vouch for that!!!