I am trying to calculate the number of hours (w/fraction, e.g. 36.25) between two datetime stamps, one of these fields may or may not be populated. I have tried multiple "TIMEDIFF()" fuctions but I cannot simply get the number of hours between the two datetime stamps. I've come close but cannot seem to get negative hours (i.e. 12:00 - 16:00 = -4).
Both fields are recognized by Domo as a date. There are times when the two values may be the same, although my attempts to filter these out has been unsuccessful.
Any suggestions woudl be appreciated. I've been working on different approaches for over 2 hours. Thanks in advance!
Below is my code thus far, I think I am close...
CASE
WHEN `start_datetime` IS NOT NULL
AND `end_datetime` IS NOT NULL
AND DATE_FORMAT(`end_datetime`, '%H:%i:%s') < DATE_FORMAT(`start_datetime`, '%H:%i:%s')
THEN ((DATEDIFF(`end_datetime`, `start_datetime`) - 1)*24)
+ ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
WHEN `start_datetime` IS NOT NULL
AND `end_datetime` IS NOT NULL
AND DATE_FORMAT(`end_datetime`, '%H:%i:%s') > DATE_FORMAT(`start_datetime`, '%H:%i:%s')
THEN ((DATEDIFF(`end_datetime`, `start_datetime`)*24)
+ ROUND(TIME_TO_SEC(TIMEDIFF(`end_datetime`, `start_datetime`))/3600,2)
END
