1 Reply Latest reply on Mar 21, 2018 10:32 PM by phan10ms

How can use Time in a conditional or case statement?

How can use Time in a case statement?

I am trying to autofill travel and per diem benefits base on times in this example I want to switch off of 4 different times to yield 4 amounts:

 case when FC_3Date <= EndDate      && EndTime >= "18:00" then 80 when FC_3Date <= EndDate      && EndTime >= "12.00" then 60 when FC_1Date = EndDate      && EndTime >= "6.00" then 40 else 20

The comparison always seems to fall through I have tried integers such as 6 and 18, decimals such as 6.00 and 18.00 and text formatted like times such as 6:00 and 18:00

I think the problem may lie in  comparing a time to a text/number. There may be additional issues in differentiating between am and pm values

• 1. Re: How can use Time in a conditional or case statement?

My guess is that you'll have to use Date Functions to parse the date data.  For an example, if you're trying to evaluate the hour of the data, your logic would go something like this.

case

when LocalHour(FC_3Date) <= LocalHour(EndDate)

&& LocalHour(EndTime) >= 18 then 80

when LocalHour(FC_3Date) <= LocalHour(EndDate)

&& LocalHour(EndTime) >= 12 then 60

when LocalHour(FC_1Date) = LocalHour(EndDate)

&& LocalHour(EndTime) >= 6 then 40

else 20