Hello,
I have some time intervals and I want to calculate the time difference between them, e.g.
In the external DB (SQL Server) these are of datatype Time(0), but when I use Integration Studio they turn into Text.
starttime endtime00:00:00 08:00:0008:00:00 16:00:0016:00:00 00:00:00
For the first 2 time intervals, I just do 8-0 and 16-8.
Of course, since they are text variables, I do substr(endtime,0,2) - substr(starttime,0,2).
But for the 3rd time interval, I don't know how to calculate it so that I get an 8 hours difference.
Any idea of how I can do this?
Btw, these time intervals can vary a lot, these are just 3 examples. But usually there will only be hours and minutes, not seconds.
Thank you!
Hello Catarina,
If I understood well your problem I think you can solve this with TextToTime() from Built in functions.
Regards
Hi Catarina,
To calculate the value correctly you also need the date, otherwise the third interval will return -16 hours instead of 8.
So you need to convert via TextToDateTime the values
Note Time_24 is a day after Time_00
Then you can use the function DiffMinutes ( I use this cause you wrote that often this values are expressend in Hours:Minutes, however you can use DiffHours,DiffSeconds..)
This is the result:
Then if you need to convert it to Time (HH:mm:ss) you can use:
TimeToText(AddMinutes(NewTime(0,0,0),"{{Minutes}}")) where {{Minutes}} is the result of previous Diff operation.
BR,
AL
Hi.
The solution that was shared above, using the function TextToDate(), seems the best way to do that.
However, if you do not want to translate the text to date, try to change, in that case, the hour "00" to "24" and do the respective calculation.
First, it's not possible to do "Substr(endtime,0,2) - Substr(starttime,0,2)." You'll get the following error:
Assuming that the interval is never larger than 24 hours, after converting the time text to a time or the hours to an integer (in this case, since you've got whole hours, it doesn't matter), you just check whether the first time is larger than the second. If it isn't, you can subtract the first from the second, and if it is, you first add 24 to the second and then subtract the first from it. So (assuming you've got integers):
if(Hour1 > Hour2, Hour2 + 24, Hour2) - Hour1