If you've ever done a SQL query in GoldMine, you might have noticed a column called RecID. What is the RecID?

 The RecID field in GoldMine is a unique identifier. It is used, internall, by SQL to index the GoldMine tables, and to provide absolutely unique record identifiers. The RecIDs are used, in some cases, to link records from different tables. As an example, when you have History records linked to Opportunity records, the relationship is based on opmgr.recid = conthist.loprecid. Also, the recid is used in GoldMine's t-log tables to log time stamp data relative to when any given record and/or field is updated.

One interesting facet of the RecID field is that it is not simply a unique identifier made up of random numbers, letters, and symbols... it is, in fact, encoded, in the first sever characters, with the creation date/time of the record. This can be decoded, to yield the date and time of record creation to within 1/50th of a second! It turns out that the coding is a simple base-36 counting system, using numerals 0 through 9, and all 26 letters of the alphabet. Without a complete digression into alternate numbering systems (programmers are familiar with base-2, or binary, base-16, or hex), it's possible to create a SQL function that you can use any time to give you the exact creation time of any GoldMine record.

To create this function, simply paste the script below into a New Query window in SQL Management Studio, and execute. Note that on the first line you must specify your GoldMine database name.

Assuming everything goes well here, you can use the function with any SQL query, from within GoldMine or anywhere else, by querying for dbo.recid2date(recid) as part of your syntax. Note, you must include the dbo. ownership part of the function identifier.

Example:

Select *, dbo.recid2date(recid) from contact2

/* ================== */
USE [mydatabase]   /* HERE YOU WANT TO SPECIFY YOUR DATABASE NAME, I.E. "GOLDMINE" /*
GO

/****** Object: UserDefinedFunction [dbo].[recid2Date] Script Date: 10/30/2011 09:47:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[recid2Date] (@recid varchar(20))
returns datetime

begin
declare @xdate datetime, @offset int
set @offset = (select DATEDIFF(ss,GETUTCDATE(),GETDATE())) /************* Number of hours offset from GMT ************/
set @xdate =
(
select dateadd(ss, @offset +

(POWER(cast(36 as bigint),6) *
case
when (select isnumeric(substring(@recid,1,1))) = 0
then (select ascii(substring(@recid,1,1))) - 55
else (select ascii(substring(@recid,1,1))) - 48
End
+
POWER(cast(36 as bigint),5) *
case
when (select isnumeric(substring(@recid,2,1))) = 0
then (select ascii(substring(@recid,2,1))) - 55
else (select ascii(substring(@recid,2,1))) - 48
End
+
POWER(cast(36 as bigint),4) *
case
when (select isnumeric(substring(@recid,3,1))) = 0
then (select ascii(substring(@recid,3,1))) - 55
else (select ascii(substring(@recid,3,1))) - 48
End
+
POWER(cast(36 as bigint),3) *
case
when (select isnumeric(substring(@recid,4,1))) = 0
then (select ascii(substring(@recid,4,1))) - 55
else (select ascii(substring(@recid,4,1))) - 48
End
+
POWER(cast(36 as bigint),2) *
case
when (select isnumeric(substring(@recid,5,1))) = 0
then (select ascii(substring(@recid,5,1))) - 55
else (select ascii(substring(@recid,5,1))) - 48
End
+
POWER(cast(36 as bigint),1) *
case
when (select isnumeric(substring(@recid,6,1))) = 0
then (select ascii(substring(@recid,6,1))) - 55
else (select ascii(substring(@recid,6,1))) - 48
End
+
POWER(cast(36 as bigint),0) *
case
when (select isnumeric(substring(@recid,7,1))) = 0
then (select ascii(substring(@recid,7,1))) - 55
else (select ascii(substring(@recid,7,1))) - 48
End
)
/50
,'1/1/1990')
)
return (@xdate)
end

GO

/* ================== */