Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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!!!

share|improve this answer
    
Unfortunately: DateTime.FromOADate(BitConverter.Int64BitsToDouble(0x01ce30e07ee3f800)) doesn't return a date in 2013. I tried swapping the two 32-bit chunks as 0x7ee3f80001ce30e0 and that throws an exception converting to a double. –  Ants Apr 24 '13 at 19:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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