CR9 IF statement - need DateTime result 1

[JoyCR9]

Crystal Reports version 9 on SQL/Oracle

My formula starts out as:
if {SERVICE_LEVEL.RESPONSE_TIME}="8 HR"
then "00:08:00:00"
else
if {SERVICE_LEVEL.RESPONSE_TIME}="4 HR"
or {SERVICE_LEVEL.RESPONSE_TIME}="4hr"
then "00:04:00:00"
else
if {SERVICE_LEVEL.RESPONSE_TIME}="2HR-PHONE"
then "00:02:00:00"
else
if {SERVICE_LEVEL.RESPONSE_TIME}="CUSTOM"
then "00:99:00:00"
else

and goes on through various times. It works fine, but my problem is the result is a string and I need a DateTime value. How do I achieve this?

This formula is placed in the visable Group Footer.

 

[hilfy]

Have you looked at using the "TimeValue" function? Since you're only looking at times here, it looks like you need something that doesn't look at the date part of the date time.

-D

 

[JoyCR9]

hilfy,

Thanks for your speedy reply. I have played with TimeValue.

2 issues:

1. Still returns response in String (ie: drag mouse over field on report and it says @formula(String).
2. if {SERVICE_LEVEL.RESPONSE_TIME}="2 HR"
then TimeValue ("02:00") from formula returns 02:00AM. I don't want AM.

What I want is 00:02:00:00 (dd:hh:mm:ss) so I can use this result with a datediff down the road in my report.

 

[synapsevampire]

1. Still returns response in String (ie: drag mouse over field on report and it says @formula(String).

What you're constructing is a string, why would you expect it not to be?

2. if {SERVICE_LEVEL.RESPONSE_TIME}="2 HR"
then TimeValue ("02:00") from formula returns 02:00AM. I don't want AM.

What would you expect 02:00 to equal? 14:00 is 2PM as it uses a 24 hour clock.

And what date are you going to add to this for future use with a datediff function, todays?

Rather than posting what doesn't work, and describing requirements using text, try posting example data and the required output.

So rather than breaking this up into various components, try:

if {SERVICE_LEVEL.RESPONSE_TIME}="8 HR" then
cdatetime(year(currentdate),month(currentdate),day(currentdate),
8,0,0)

Substituting currentdate with whatever date field or foruma you want.

You might do something clever with extracting the numeric portion of your field to derive the time as well, hence simplifying the code to not have to code for every value:

whileprintingrecords;
numbervar Hours := val("8 HR");
cdatetime(year(currentdate),month(currentdate),day(currentdate),
Hours,0,0)

-k

 

[synapsevampire]

Opps, should have mentioned to use your field for val("8 HR"):

whileprintingrecords;
numbervar Hours := {table.yourfield};
cdatetime(year(currentdate),month(currentdate),day(currentdate),
Hours,0,0)

Substituting your date field for currentdate

Now you have a real datetime available.

-k