Date help

[old123]

I am using CR XI and am connecting to an AS/400. The date and time formats are numeric

My field is Hire date & it is store tlike 42180 this is mm/dd/yy
so for 04/21/1980 it is 42180
04/23/2000 it is 42300
12/29/2003 it is 122903

I need to convert AS/400 numeric dates into a Crystal date format.all other Date records are stored as yymmdd, however this field is stored like this. I tried numbertodate function but did'nt work.


Need help

 

[Madawc]

Are you saying you have a mix of MMDDYY and DDMMYY dates? This would be tough for dates like 030405, unless there is something that distinguishes the records.

Assuming there is, you can split using commands like LEFT, MID and RIGHT, and there is also LENGTH to tell whether the field is 5 or 6. Then use Date(Year, month, day) to turn the parts back into a real date.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Try:

numbervar yr := if val(right(totext({table.number},"000000"),2)) < 50 then
2000 + val(right(totext({table.number},"000000"),2)) else
1900 + val(right(totext({table.number},"000000"),2));
numbervar mo := val(left(totext({table.number},"000000"),2));
numbervar dy := val(mid(totext({table.number},"000000"),3,2));
date(yr,mo,dy);

I just picked 50 arbitrarily to distinguish dates from the 1900's vs 2000's, so substitute what makes sense to you.

-LB