AS400 null date value interpretation CR 10

[morristraveller]

My IBM ISO dates storred on an AS400 (ISo date Format yyyy-mm-dd)are being interpreted correctly by CR 10 until a null date value is encountered the format for an AS400 ISO null date value is 0001-01-01. The date is being transformed into 2001-01-01 on my reports. I've reviewed the regional settings for Windows XP, the options settings within CR 10, the IBM iSeries access for Windows set up options and can't figure out why Crystal is interpreting the null century as 20. I didn't have this problem with 8 or 8.5 the null there was interpreted ans an empty date.

H E L P !! Pulling my hair out here !!!

 

[GJParker]

Go to File menu -> Report options -> select Convert database NULL values to default.

If this doesn't work try conditional formulas i.e.

If Not Isnull({Table.Date}) Then {Table.Date}

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[morristraveller]

Thanks Gary. I tried those and I still have 2001 in my null date field. Any other ideas?

 

[lbass]

Why not hardcode the null date? First, uncheck "Convert null values to default" in file->report options. Then create a formula:

if isnull({table.date}) then date(0001,01,01) else
{table.date}

If you don't want a result to appear for nulls, then use date(0,0,0)

-LB

 

[morristraveller]

Thanks Ibass, but your suggestions won't work either as Crystal is receiving or reading the null date of 0001-01-01 as 2001-01-01 and so there is no null value to process. and if I change 2001-01-01 to a null with code I will be eliminating any valid 2001-01-01 date and I have no way to differentiate between valid 2001s and invalid 2001s.

I'm having the same problem on Windows 2000.

In Windows 2000-> Control Panel->Regional and Language Options->Customize Regional Options->Date the calendar option 'When a two digit year is entered interpret it as a year between 1930 and 2029 in my case my 2 digit year is 01 '(I don't know where the century is going) so my year in Crystal becomes 2001. I'd like to rule out this as a source of my problem I'm guessing it's something to tweak in the registry but I don't know what.

Either way I think the problem is outside of Crystals sphere of control. Does anyone out there have any experiance with Crystal to AS400 ODBC connectivity?

'Going bald '

 

[cfederman]

Hi morristraveller,
Wanted to let you know that I am experiencing the same thing that you have outlined. Only I am running Crystal 9, connecting to a DB2 ODBC connectivity. My db has records populated with 0001-01-01 and Crystal is returning 2001-01-01.

I have been searching for a "minimum" date or year that Crystal will handle and have not been able to find any documentation. If you've gotten any further with this please let me know.

Thanks, you're not alone!

 

[jayster7]

I also am having problems, but with the help of GJPArker I got some data by using < date(0). I'm using CR10 and I'm told that the Date(0) is equal to 12/31/1899 so now when I set the Report Options to "Convert null values to default", I did get data using
{your.datefield} < Date(0).
Don't know how good yet, but I'd been pulling m hair out all morning!

 

[craigsean]

You need to change the settings in your ODBC connection. Go to the ODBC administration tool, select the connection you want to change and click configure. Go to the server tab and click advanced. Change the date format to dd/mm/yy (*DMY)

 

[fredp1]

There is a posted Hotfix from BO today that address the problem.

http://ftp1.businessobjects.com/webprod/hot_fixes/MHF_readme/Crystal10/cr10win_en.pdf

ADAPT00175452 Patch ID: 36375929



Cheers
Fred

 

[paulmarr]

I have found that using the date 0100/01/01 works with AS400 DB2.

Cheers,
paulmarr