Converting a numeric date into an actual date & numeric time to a time 2

[bzzyplayin]

I am using CR 8.5 and am connecting to an AS/400. The date and time formats are numeric (stored in separate fields).

I need to convert AS/400 numeric dates into a Crystal date format. Date records are stored as yymmdd, however since the field is numeric, the leading zero for the year is dropped off.
i.e.
21231 = year 2, month 12, day 31
30105 = year 3, month 1, day 5

Any ideas on how to add the leading zero (to the year) and convert it to a date format?

I also need to convert AS400 numeric time fields into a time format. Time records are stored in military time (in a numeric field).
i.e.
600 = 6:00 am
700 = 7:00 am
1000 = 10:00 am
1100 = 11:00 am
2200 = 10:00 pm
2300 = 11:00 pm
2400 = 12:00 pm

Any ideas on how to convert the records into a time format?

Thanks, in advance!
Kim

 

[dgillz]

Can you show me a date in the 1990s versus a date in the 2000's so I can see the difference? Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[bzzyplayin]

The field is a length of 6. Dates are listed in Crystal as:

981231 (December, 31 of 1998)
yymmdd

990115 (January, 15 of 1999)
yymmdd

21231 (December, 31 of 2002)
ymmdd

30115 (January, 15 of 2003)
ymmdd

I have been able to display the dates correctly with the format below, but the value is still a string. This worked for me temporarily. I would prefer to take advantage of Crystal’s date logic and ability to use date formatting.

I am very new to Crystal (2 months) and have just started learning/understanding Crystal Syntax and have since came up with the following formulas:

Name: {@Date to Text}
Formula: ToText({file.datefield},0,"&quot


Name: {@Date Converted}
Formula: stringvar Y:= {@Date to text} [1 to 1];
stringvar M:= {@Date to text} [2 to 3];
stringvar D:= {@Date to text} [4 to 5];
dateserial (tonumber(Y),tonumber(M),tonumber(D))

This formula is close, but ends up returning 3 for year 2003 since the leading zero for the year was never considered. The value returned looks like this: 1/17/3 for January 17 of 2003. I would prefer that it would return 1/17/03. What happens is, if I change the formatting I can never get it to recognize the date as 2003.

Below is a sample of the AS/400 values and each of the formula values for January 17 of 2003.

AS/400 Time
700.00

AS/400 Date
30,117.00

Date String
01/17/03

Date to Text
30117

Date Converted
1/17/3

I am getting close on converting the date, but would still love to hear from any of you on a solution or any better ideas.

And if you have any thoughts regarding the time.. I am at a loss.

Thanks is advance!
Kim

 

[dgillz]

On the date, try this formula:

If {YourField}<91231 then Numbertodate({YourField}+20000000) else Numbertodate({YourField}+19000000)

Numbertodate() is a downloadable function on the crystal decisions website and takes an 8 digit integer in a YYYYMMDD format and converts it to a real date. Then all you need to do it format this to your liking. The above formula will be good until the end of the decade.

The Numbertodate UFL is here:

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufltdate.exe.asp

I will wok on the time piece of this. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[bzzyplayin]

Thanks dgillz! The NumberToDate formula worked perfectly!

Question: I publish all of my reports to Crystal Enterprise 8.5. Do you know which directory I need to put the dll's in on the server machine?

Will the date be displayed correctly for all users running the report from CE regardless if they have the dll's on their own machine? Or will I run into problems based upon who runs the report?

Again, Thank you so much!
-Kim

 

[dgillz]

Kim,

I do not know, but if I had to guess my guess would be winnt\crystal on your CE machine. You might post this question in the CE forum. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Naith]

You need to install the NumberToDate UFL on the CE server, just like dgillz has suggested, and then that's it.

You don't need to put it on all the users machines.

Naith

 

[bzzyplayin]

Thanks Naith. I will install it on the CE machine tomorrow and test it out

Any thoughts on converting a numeric military time field to a Crystal time field?

 

[dgillz]

Time(HH,MM,SS) will work. How is your time is stored? as a number? Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[bzzyplayin]

I just figured it out. It took 3 formula's but it worked. The time field in the database in numeric (in a military time format).

I have listed the 3 formulas below:

//Formula 1: Time to Text
totext({file.field},0,&quot;&quot

//Formula 2: Time insert colon
if len({@Time to Text})=4
then {@Time to Text}[1 to 2]+&quot;:&quot;+{@Time to Text}[3 to 4]
else
{@Time to Text}[1]+&quot;:&quot;+{@Time to Text}[2 to 3]

//Formula 3: Time Converted
time({@Time insert colon})

The result is a Crystal time field.
whew! Finally!

You may offer a better solution... I am just THRILLED that I have an actual time to work with.

 

[dgillz]

You really do not have a time, you have a text string that looks like a time. With a real time field, you could perform math on it.

What are you going to do with your &quot;time&quot; field? Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com