C++ V's VB(A)

[Dave_cha]

Hi folks,

I'm trying to read some records from an SQL database. One of the fields is an integer field though the data corresponds to a given date and time stamp.

When the data is viewed through the vendors application it uses C++ to convert the integer to a legible date and time. The C++ code is as folows;

CTime ExpandTime(int time)
{
WORD year;
BYTE hour,min,sec,month,date;

sec = (BYTE) (time & 0x3F);
time >>= 6;
min = (BYTE) (time & 0x3F);
time >>= 6;
hour = (BYTE) (time & 0x1F);
time >>= 5;
date = (BYTE) (time & 0x1F) + 1;
time >>= 5;
month = (BYTE) (time & 0x0F) + 1;
time >>= 4;
year = (WORD) (time & 0x3F) + 1996;
CTime t(year,month,date,hour,min,sec);
return t;
}

For example, 609537500 is converted to 11-Feb-2005 12:55.28

Does anyone know if and how I could use VBA to convert this integer to a legible date field? I'm trying to build an Access front to this SQL db to provide tailored reports. The third party application has limited reporting options.

Thanks,

Dave

 

[FoFa]

Typically as long as you know the BASE date, you can use calculate it using normal access functions.
As an example, I use this function to convert INT dates from our ARS system
Function FromARSDate(DateIn As Long) As Date
FromARSDate = DateAdd("s", DateIn, #1/1/1970#)
End Function

 

[The_Doc_Man]

Your problem is three-fold...

First, this is a bit-packed compression of times as component fields, so it is non-linear with respect to time.

Second, it uses a different reference date than windows does.

Third, it is an integer whereas Access time is actually a DOUBLE quantity. (As a side note, C++ Integer is the same as Access LONG, which might mislead you badly.) So you have a double-barreled type conversions.

Therefore, your solution involves three steps.

1. Unpack.
2. Convert to Double
3. Adjust for different base date.

Steps 2 & 3 could be swapped. Step 1 must come first either way. In step 1, the MOD function is your friend.

You need to build a public function in a general module. I'm going to be rather "loose" here in my suggested module. I'm going to unpack the integer, convert it to a string, and then do steps 2 and 3 at the same time using the CDate function.

Public Function CvtCP2Time( CP2Time as LONG ) as Date

Dim TFrag as Long, Secs as Long, Mins as Long, Hrs as Long
Dim Days as Long, Mths as Long, Yrs as Long
Dim stDate as String

secs = CP2Time mod 64           ;split out seconds (same as & x03F )
tfrag = CP2Time / 64               ;shift by 6 bits ( >>=6 operator)
mins = tfrag mod 64                ;split out minutes
tfrag = tfrag / 64                    ;shift by 6 bits ( >>=6)
hrs = tfrag mod 32                  ;get hours (same as & x01F )
tfrag = tfrag / 32                    ;shift by 5 bits ( >>=5)
days = 1 + ( tfrag mod 32 )      ;get day number
tfrag = tfrag / 32                    ;shift by 5 bits ( >>=5)
mths = tfrag mod 16                ;this looks wrong but is not - bear with me
tfrag = tfrag / 16                    ;shift by 4 bits ( >>=4)
yrs = tfrag + 1996                   ;reconstitute the year

stDate = Format$( days, "##" ) ; string is dd
stDate = stDate & "-" & Mid( "JanFebMarAprMayJunJulAugSepOctNovDec", 1=(3*mths), 3 )                         ; string is dd-MMM
stDate = stDate & Format$( years, "####" ) ; string is dd-MMM-yyyy
stDate = stDate & " " & Format$( hrs, "##" ) ; string is dd-MMM-yyyy hh
stDate = stDate & ":" & Format$ (mins, "##" ) ; now dd-MMM-yyyy hh:nn
stDate = stDate & ":" & Format$( secs, "##" ) ; now dd-MMM-yyyy hh:nn:ss

CvtCP2Time = Cdate$( stDate ) ; now is in Access/Windows date format

End Function

Play with this a bit. That long one that converts the month number to a month name should be on one line if you can stand it. Line up the comments, this method here doesn't really keep formatting that pretty.

 

[Dave_cha]

Thanks guys.

I'll play around with the code code you supplied Doc_Man and let you know how I get on.

Rgd's,

Dave

 

[The_Doc_Man]

Just a note... looking at it, I realize I've changed programming languages one too many times. The comment mark I used was ; but should have been '

Just to warn you....

Some days I can't even edit without a major problem shifting gears. Must be some grit in the gearbox...

 

[Dave_cha]

No problem Doc Man.....I spotted that and corrected.
I tried it on Friday evening and it worked perfectly. Now all I have to do is get my head around the detail of the function.

Thanks for you help with this.

Rgd's,

Dave