DateTimeValue problem

[Phillipg]

We have a program that calls reports designed in CR7 to view and print. I've designed a report in CR8.5 and saved as CR7 (have tried 8.5 also) but get an error when the report starts to load. The error is
"Error in formula <AV>
'DateTimeValue({Incident.Timeinservice}-{Incident.TimeAtDestination})'
The remaining text doex not appear to be part of the formula."
Both fields are DateTime fields as 6/21/2004 04:30:00PM.
This formula works in crystal but not in the program. If the formula is removed from the report, the program opens the report, no problems.
Any help with will be appreciated. Thanks

Phillipg

 

[lyanch]

What are you trying to accomplish with the formula? DateTimeValue returns a datetime, it doesn't take one as input.

Lisa

 

[synapsevampire]

Perhaps datetimevalue wasn't supported in CR 7, and it is only available when using Crystal syntax.

You might try cdatetime and see if that works for you.

Also it would be interesting to know what the intent is.

I think that your formula will return the number of days difference, not the day and time difference, is that the intent?

There's also the datediff function, and if it didn't exist in CR 7, then you might use the UFL, here's a listing of all the UFL's, including UFLDTDIF.EXE:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_ufls.pdf:

-k

 

[Phillipg]

Normaly the difference in the two values is only a few minutes. It is formated to show only the time in a 24 hour time format, example, 0.03. It is the results (in time) of a subtraction of the two fields.
Thanks, I'll try your suggestion.

Phillipg

 

[Phillipg]

I tried cdatetime without any luck.
Also, it won't process whileprintingrecords either. Thanks

Phillipg

 

[lbass]

If you want the datetime difference in decimal format (representing a fraction of a day), I think you can just use:

{Incident.Timeinservice}-{Incident.TimeAtDestination}

If you wanted to format it in hh:mm:ss, you would then multiply the above * 86400 to convert it to seconds, and then use SynapseVampire's FAQ formula (following) to convert it to hh:mm:ss (although this assumes that truncate and remainder are available functions in 7.0).

numberVar dur := ({@datetime}-{Orders.Order Date})* 86400; numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

-LB

 

[synapsevampire]

I believe Truncate and Remainder were in CR 7, here's a post similar to LB's in scope (Remedy related):

http://corp.elegantsp.com/pipermail/remedy/2000-May/009967.html

Not sure what "Also, it won't process whileprintingrecords either." means, it returns an error? Where were you using it?

-k

 

[Phillipg]

I have found a solution. It is a modified version of Lbass's recommendation. it seams to work fine. The 'Whileprintingrecords' function also returned an error so I removed it. The formula I used is as follows.
Thanks to every one.
Phillipg

Numbervar H :=0;
Numbervar M :=0;
numbervar S :=0;
Stringvar hr :="";
Stringvar mn :="";
Stringvar ss :="";
Stringvar totaltime :="";
S:=(time({INCIDENT.TIMEINSERVICE})-(Time({INCIDENT.TIMEATDESTINATION})));
M:= truncate(S/60);
if (S>59) then S:=remainder(S,60);
H:= truncate (M/60);
if (M>59) then M:= remainder (M,60);
hr:=totext(H,"00");
mn:=totext(M,"00");
ss:=totext(S,"00");
totaltime:=(hr+":"+mn);
totaltime

Phillipg