The hardest part with creating reports is when you have to deal with inconsistent data in fields. When expecting time to be represented as hh:mm:ss (e.g. 01:02:03) and instead you receive a mix of h:mm:ss (1:02:03) and mm:ss (62:03), it’s easiest to use a function that converts the inconsistent time data to a value that’s easier to report on, or run subsequent calculations on.
In the example below, the SQL function does a quick check to count the number of occurrences of the time separator, in this case ‘:’, and then proceeds to calculate each time partition as the corresponding seconds value.
[snippet slug=convert-hhmmss-to-seconds lang=sql]
Any suggestions for a more efficient way to calculate the final seconds conversion?