5 Replies Latest reply on Sep 24, 2015 12:51 AM by softi88365823

# How do I convert a hex date?

When outputting meta data from SharePoint Foundation 2010 using cfsharepoint I am noticing datetimes are being converted to the following format:  0x01cf2d81|0x71c80a00

How do I convert this to a regular date using ColdFusion?  I have found some references to this issue here http://sharepoint.stackexchange.com/questions/66602/dates-and-times-in-properties-vs-field s/91069#91069 which shows a solution like this:

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

```

But I have no idea how to do this in ColdFusion.  Apparently the hex value represents the number of ticks since 1st Jan 1600.  Crazy, I know.

Any ideas??  Thanks.

• ###### 1. Re: How do I convert a hex date?

<!---

Assumption: 0x01cf2f8e|0x6e559e00 stands for the Hex number 1cf2f8e6e559e00. That is the number of ticks.

--->

<!--- Conversion of the number of ticks, 1cf2f8e6e559e00, from Hex to Base 10, remembering that F=15, E=14, D=13, C=12, etc  --->

<cfset numberOfTicks = 14*16^2+9*16^3+5*16^4+5*16^5+14*16^6+6*16^7+14*16^8+8*16^9+15*16^10+2*16^11+15*16^12+12*16^13+1*16^14>

<!---

By definition,

1 second = 10 000 000 ticks.

--->

<cfset numberOfWholeMinutes = round(numberOfTicks/(10000000*60))>

<!--- Starting datetime is 1st January 1600 --->

<cfset zeroDateTime=createdatetime(1600,1,1,0,0,0)>

<cfoutput>#newDateTime#</cfoutput>

[Sidenote: I attempted to use the function inputBaseN("1cf2f8e6e559e00",16) to convert from Hex to base 10, but it gave the wrong result! I will now report this as a bug. I would suggest that you write your own function to do the conversion.]

• ###### 2. Re: How do I convert a hex date?

BKBK wrote:

Sidenote: I attempted to use the function inputBaseN("1cf2f8e6e559e00",16) to convert from Hex to base 10, but it gave the wrong result! I will now report this as a bug.

• ###### 3. Re: How do I convert a hex date?

Thanks for your help with this, BKBK.  I have been working on it for a day, and discovered some useful info and eventually found an easier way to accomplish this.

Below you will see the number of ticks can actually be retrieved by using the Java parseLong() method.  I have no idea what's up with inputBaseN or why it's not working for this scenario, but we can forget that for now.  Also, I discovered the Windows system time does not start at 1600, it starts at 1601.  The guy in the article I linked to actually got it wrong, so apologies for any confusion about that!  Finally, I convert the date to local time.

Below is the full code to convert the SharePoint hex date to regular date using ColdFusion.  Hope this helps someone else in a similar situation.

```  <cfset hexdate = #Replace(meta.vti_imgdate, "0x", "", "All")#>
<cfset hexdate2 = #Replace(hexdate, "|", "", "All")#>
<cfset str = "#trim(hexdate2)#">
<cfset numberOfTicks = createObject("java", "java.lang.Long").parseLong( str, 16 )>
<!--- By definition, 1 second = 10,000,000 ticks but need to divide by 60 and call it minutes or number will be out of range for ColdFusion. --->
<cfset numberOfWholeMinutes = round(numberOfTicks/(10000000*60))>
<!--- Starting datetime is 1st January 1601, which is the Win32 epoch time --->
<cfset newDateTime = dateadd("n",numberOfWholeMinutes,"01/01/1601 12:00 AM")>
<cfset newDateTimeUTC = DateFormat(DateConvert("utc2Local",newDateTime), "mmmm dd, yyyy")>
```

By the way, here is a cool  article about a guy that goes into something similar: http://ruthsarian.wordpress.com/category/programming/coldfusion/

• ###### 4. Re: How do I convert a hex date?

<cfset newDateTime = dateadd("n",numberOfWholeMinutes,"01/01/1601 12:00 AM")>

The 3rd argument of the dateAdd function should be a datetime object. So you could improve it with something like this