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

    How can use Time in a conditional or case statement?

    mbath61 Apprentice

      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?
          phan10ms Apprentice

          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