Hundred Year Format

[racskelly]

does anyone know how in crystal to convert as400/db2 dates (that are formated using the hundred year format) to normal dates...

for example the database returns my dates similar to 921014

 

[dgillz]

And what date does 921014 represent?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[racskelly]

thats the question... the database stores dates in the hundred-year format...but i have no idea how to convert 921014 to a normal date format...

 

[dgillz]

So you have no idea what 921014 means as a date? If not how could anyone convert it? Have you tried the psychic hotline?

Seriously you need to understand what the # means, then a formula to get it to a real date cannot be too tough.

I have never heard of a hundred year format, I have been working with crystal for 11 years.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[racskelly]

921014 is a date stored in a hundred year format... thats what it means. its one of three possible date formats in AS400

 

[IanWaterman]

I thought AS400 used seconds from midnight 1st Jan 1970.

There is a formula for converting that but for the life of me I can not remember.

Ian

 

[AndyMc]

I believe it is the # of days since December 31, 1899.

Andy

 

[dgillz]

it is neither of the 2 solutions offered by Ian or Andy. Find out how to translate this to a date and the formula can be written.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[racskelly]

that is what i'm trying to figure out...

 

[racskelly]

maybe format is YYMMDD

 

[dgillz]

Is this a number or a string field?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[racskelly]

Number

 

[AndyMc]

racskelly,

If it's YYMMDD, you could try (use whatever you want in place of "33" below to determine when to use 19xx or 20xx):

stringvar DtStr := totext({table.field},0,"");
numbervar yr := val(left(DtStr,2));
numbervar mt := val(mid(DtStr,3,2));
numbervar dy := val(right(DtStr,2));

if yr > 33 then
date(1900+yr,mt,dy)
else
date(2000+yr,mt,dy)


Andy

 

[LyleU]

Maybe this will get things started. I found it in another discussion group so I can't vouch for its authenticity!

...the best way to convert a date to a Hundred-Year format is with the following formula: result = date(datefield + (days((date('1899-12-31')))))
The base date is Dec 31, 1899. the formula works for SQL, Queries, and RPGILE programming languages. I would assume it would still apply to any other programming language, you should follow the specific format for those languages.

Lyle

 

[dgillz]

If you add 921014 days to 12/31/1899, you get a date of 8/25/4421. As I mentioned, this is not the solution.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?