ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> Payroll >> 19xx vs 20xx
http://www.istechforum.com/YaBB.pl?num=1228758975

Message started by Lenette on 12/08/08 at 10:56:15

Title: 19xx vs 20xx
Post by Lenette on 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

Title: Re: 19xx vs 20xx
Post by kevind on 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.

Title: Re: 19xx vs 20xx
Post by 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

Title: Re: 19xx vs 20xx
Post by kkmfg on 12/09/08 at 07:27:03

Lenette wrote:

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

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

Title: Re: 19xx vs 20xx
Post by kevind on 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 (http://en.wikipedia.org/wiki/Endianness)' (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!!

Title: Re: 19xx vs 20xx
Post by 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?

Title: Re: 19xx vs 20xx
Post by kkmfg on 12/22/08 at 14:26:54

deburr wrote:

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.

Title: Re: 19xx vs 20xx
Post by kevind on 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};

Title: Re: 19xx vs 20xx
Post by deburr on 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

Title: Re: 19xx vs 20xx
Post by deburr on 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

Title: Re: 19xx vs 20xx
Post by kevind on 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.

Title: Re: 19xx vs 20xx
Post by kkmfg on 12/31/08 at 06:55:05

kevind wrote:

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.