ISTech Support Forum - 19xx vs 20xx

Lenette Senior Member That's why they call it work!! Posts: 461 Gender:	19xx vs 20xx 12/08/08 at 10:56:15 While creating a simple Crystal Report to obtain my employees date of birth I notice some of them aren't born yet! Crystal Reports captures 1/2 of them with the year of birth as 20xx instead of 19xx. When look at them in BKPRMSTR the calendar shows them in 19xx. I'm confused. Any thoughts from anyone? Lenette
Back to top	Version: Current (always current on patches) Build - 05/21/19 T7-7iR11 Pervasive 12.00 - EVO seats 15 DC seats 5 DBA since 2002 Lenette Egan McDantim Inc Helena, Montana IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: 19xx vs 20xx
Reply #1 - 12/08/08 at 13:10:24

Lenette,
Somewhere in the data string (database, ODBC driver, TAS, ??), some 2 digit date values come across as year 2000 instead of 1900. I tried editing my Regonal settings on my XP Pro client, but it did not have any affect, so it must be happening higher upstream.

An easy way to deal with this at the Crystal level is to define a Function with the following lines:
Code:

if {BKPRMSTR.BKPR_EMP_BDAY} > CurrentDate then 
  CDate(DateAdd('yyyy', -100, {BKPRMSTR.BKPR_EMP_BDAY}))

this will subtract 100 years from any birthdates that look like they are in the future.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

Lenette Senior Member That's why they call it work!! Posts: 461 Gender:	Re: 19xx vs 20xx Reply #2 - 12/08/08 at 15:52:19 thanks... i will give it a try. I noticed that it is only with employees born in the 40s & 50s that show up as 20xx. Lenette
Back to top	Version: Current (always current on patches) Build - 05/21/19 T7-7iR11 Pervasive 12.00 - EVO seats 15 DC seats 5 DBA since 2002 Lenette Egan McDantim Inc Helena, Montana IP Logged

kkmfg

Senior Member

Ghost of the code

Posts: 411
Gender: male

Re: 19xx vs 20xx
Reply #3 - 12/09/08 at 07:27:03

Quote from Lenette on 12/08/08 at 15:52:19:

thanks... i will give it a try. I noticed that it is only with employees born in the 40s & 50s that show up as 20xx.

Lenette

Bingo! Yahtzee! We have a winner!

Seriously, this is expected behavior unfortunately. You see... A lot of programs used two digits to store the years. Some programs solved the Y2K glitch by treating all two digit years from 00-59 as if they were really 20XX and all dates 60-99 as 19XX. That was one of the two ways to solve the glitch. The other being actually switching to four digit dates. We won't live to see that backfire on us. Wink

But, you will see weird results like this from some programs if you have dates with only two digits for the year.

Back to top

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: 19xx vs 20xx
Reply #4 - 12/10/08 at 10:51:08

I dug down to the byte level in the BKPRMSTR table.

At byte offset 0639 hex, the Birthdate is stored in the 4 byte Btrieve DATE format.
For you programming types, it is:
struct
{
   Day : char;
   Month : char;
   Year : short; /* (16bit integer or Small Integer) */
}

Using the Pervasive Function Executor, I read these 4 bytes at offset 0x639:
   0x15 0x07 0xEA 0x07
at offset 0x0639 for an employee that has a birthdate of 07/21/26 as shown on the PR-A screen.

Since Anything larger than char is stored 'little endian' (Thanks Intel), we need to switch the last two bytes so that we can convert the hex year to decimal:
   0x15 0x07 0x07 0xEA

These 4 bytes convert as follows:
0x15 = 21 - Day
0x07 = 7 - Month
0x07EA = 2026 - Year

So, Pervasive has the ability to store from year -32767 to 32767 (signed 16bit Integer).
According to the Pervasive sqlref manual, section 'Pervasive.SQL Supported Data Types'
they support from year 0001 to year 9999.

What this means is that since the PR-A screen only allows us to enter a 2 digit year, either the PR-A application or TAS is deciding that '26' means '2026' and is storing that value into the Btrieve record.

The simple Crystal function shown above should always work for now, given this behavior.

So there you have it, everything you always didn't want to know about the Pervasive 4 byte DATE format!!

Back to top

« Last Edit: 12/10/08 at 17:26:29 by kevind »

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

deburr Senior Member Posts: 487 Gender:	Re: 19xx vs 20xx Reply #5 - 12/22/08 at 14:23:18 Kevin I ran into the same thing as Lenette today and tried using the formula you suggested in Crystal but I can't get it to work. I'm not strong in Crystal and I'm not sure how to correct the 2nd half of the formula. Crystal reports 5.0 doesn't seem to understand "DateAdd" Any suggestions other than to upgrade my crystal reports?
Back to top	Thanks, Steve Cyr Deburring House Inc. EVOERP Version 2018.2 PSQL 12 IP Logged

kkmfg

Senior Member

Ghost of the code

Posts: 411
Gender: male

Re: 19xx vs 20xx
Reply #6 - 12/22/08 at 14:26:54

Quote from deburr on 12/22/08 at 14:23:18:

Kevin

I ran into the same thing as Lenette today and tried using the formula you suggested in Crystal but I can't get it to work. I'm not strong in Crystal and I'm not sure how to correct the 2nd half of the formula. Crystal reports 5.0 doesn't seem to understand "DateAdd" Any suggestions other than to upgrade my crystal reports?

You could probably just change the entry in the table manually. From then on out it would be 19XX instead of 20xx but who knows if a DBA app would ever reset it again.

Back to top

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: 19xx vs 20xx
Reply #7 - 12/22/08 at 15:43:30

Ok, Try this Formula for Crystal Versions Prior to Version 8:

Code:

//
// Algorithm Borrowed from http://www.kenhamady.com/form05.shtml
//
WhileReadingRecords;
DateVar Xfield := {BKPRMSTR.BKPR_EMP_BDAY}; 
NumberVar Xmonths := -(12 * 100);

DateVar ND3 := Xfield - Day(Xfield) + Truncate(Xmonths*30.4375+15);
DateVar ND4 := ND3 + 30;
DateVar Begin3:= Date(Year(ND3),Month(ND3),1);
DateVar Begin4:= Date(Year(ND4),Month(ND4),1);

if Xfield > CurrentDate then
  If Begin4 > Begin3 + Day(Xfield) - 1 then
    Begin3 + Day(Xfield) - 1
  else
    Begin4 -1
else
  Xfield;

BTW: I did not copy in the entire function code for the version that uses DateAdd, It should be:
Code:

if {BKPRMSTR.BKPR_EMP_BDAY} > CurrentDate then 
  CDate(DateAdd('yyyy', -100, {BKPRMSTR.BKPR_EMP_BDAY})) 
else
  {BKPRMSTR.BKPR_EMP_BDAY};

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

deburr Senior Member Posts: 487 Gender:	Re: 19xx vs 20xx Reply #8 - 12/23/08 at 10:24:58 Kevin Many thanks for your trouble. I plugged that in and it works great! I'll need to study it when I get more time so I can learn the logic and syntax. That is a great website you referenced. That will come in very handy for me and my old Crystal reports. Steve
Back to top	Thanks, Steve Cyr Deburring House Inc. EVOERP Version 2018.2 PSQL 12 IP Logged

deburr

Senior Member

Posts: 487
Gender: male

Re: 19xx vs 20xx
Reply #9 - 12/30/08 at 14:59:45

Kevin

I was premature with my comment. The formula converted the dates when I pasted it using the data already read. When I refresh the report I still get an error. Some of that may have to do with the new update. I can no longer browse many fields that I know there is data, like SS number and Bday. I am hoping Lynn finds a solution for me before I run the next payroll in the morning. I may have to restore my old data and use the old payroll program.

Steve

Back to top

Thanks,

Steve Cyr
Deburring House Inc.
EVOERP
Version 2018.2
PSQL 12

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: 19xx vs 20xx
Reply #10 - 12/30/08 at 15:11:55

Steve,
If the fields in the tables were changed by the update, you will need to run ODBCDDF from
UT-A in order to update the ODBC DDF files with the new field and table definitions.

Also, remember that some payroll fields have now been encrypted so that they are secure from casual file viewing.
This will also make these fields unusable for crystal reports since they are not longer plain text readable.

ISTECH will probably need to provide some kind of decryption algorithm so that we can create formulas in crystal
to show the actual field value.

If we want fields secure from ad-hoc reporting, we cannot just access them for our reports.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

kkmfg

Senior Member

Ghost of the code

Posts: 411
Gender: male

Re: 19xx vs 20xx
Reply #11 - 12/31/08 at 06:55:05

Quote from kevind on 12/30/08 at 15:11:55:

ISTECH will probably need to provide some kind of decryption algorithm so that we can create formulas in crystal
to show the actual field value.

Chances are they used something off the shelf. I hope it's at least AES128. If it is then the decryption algo will do you no good. You need the key. And if they give you the key then anybody and their brother could decrypt it and then the encryption is worthless. As it stands the program has to know the key and so you've already sort of got it. Currently it's something of a security through obscurity I'd think.

Back to top

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10

IP Logged