Skip to main content
D
DCwebGuy
Inspiring
February 21, 2014
Answered

How do I convert a hex date?

  • February 21, 2014
  • 2 replies
  • 1882 views

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

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.

This topic has been closed for replies.
Correct answer DCwebGuy

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/

2 replies

A
Anonymous
September 24, 2015

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

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
February 22, 2014

<!---

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
Community Expert
BKBKCommunity Expert
Community Expert
February 22, 2014

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices