How do I convert a hex date?

[DCwebGuy]

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-fields/91069#91... 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);

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

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.

[BKBK]

<!---

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

<cfset newDateTime=dateadd("n",numberOfWholeMinutes,zeroDateTime)>

<cfoutput>#newDateTime#</cfoutput>

My answer is: 2013-02-21 05:25:00

[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.]

[BKBK]

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.

If you wish to vote or comment, the link is: https://bugbase.adobe.com/index.cfm?event=bug&id=3712098

[DCwebGuy · Accepted solution]

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/

[BKBK]

<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

<cfset newDateTime = dateadd("n",numberOfWholeMinutes,createdatetime(1601,1,1,0,0,0))>

[Guest]

You can use Integer.parseInt() function or java.lang.Long.parseLong(String s)