**Introduction**

This will show how to format seconds into HH:MM:SS using a custom SQL table in the ElastiCube Manager.

**Purpose/Benefits**

Using SecondDiff() returns the difference between two dates in seconds, but is not very human readable. By converting to HH:MM:SS format, the value is much more easily understood.

**Steps**

#### Step 1

In the ElastiCube Manager, click "Add Data" and select "Custom SQL Expression". Name the table something appropriate to your data model.

#### Step 2

Add the following SQL query, changing out the fields YourUniqueIDField, YourDateTime1, and YourDateTime2 for the fields in your data model.

select YourUniqueIDField ,CASE WHEN length(b.Hours) = 1 THEN '0' + Hours ELSE b.Hours END + ':' + CASE WHEN length(b.Minutes) = 1 THEN '0' + Minutes ELSE b.Minutes END + ':' + CASE WHEN length(b.Seconds) = 1 THEN '0' + Seconds ELSE b.Seconds END AS FormattedDiff FROM ( select YourUniqueIDField ,tostring(floor(a.SecDiff/3600)) AS Hours ,tostring(floor(a.SecDiff%3600/60)) AS Minutes ,tostring(floor(a.SecDiff%60)) AS Seconds FROM ( SELECT YourUniqueIDField, toint(SecondDiff(YourDateTime1,YourDateTime2)) AS SecDiff FROM YourTable ) a ) b

#### Step 3

After committing these changes, create a relationship joining YourUniqueIDField back to the original table containing your dates.

**An Important Note About Modulo**

The modulo operator (designated by the % sign) functions *only* on integers. By default, SecondDiff() returns a float. Converting to an integer is necessary for this and other functions that return float data types when using modulo. ToInt() can be used to accomplish this conversion.

**Additional Information**

For fun, this calculation can also be executed without using modulo, but it becomes more complex. The underlying concept is to manually calculate the remainder value left over from the calculation of the number of hours given by SecondDiff(). Similarly, the next step is to calculate the left over seconds from the calculations from both the number of hours and the number of minutes.

select YourUniqueIDField ,CASE WHEN length(b.Hours) = 1 THEN '0' + Hours ELSE b.Hours END + ':' + CASE WHEN length(b.Minutes) = 1 THEN '0' + Minutes ELSE b.Minutes END + ':' + CASE WHEN length(b.Seconds) = 1 THEN '0' + Seconds ELSE b.Seconds END AS FormattedDiff FROM ( select YourUniqueIDField ,tostring(floor(a.SecDiff/3600)) AS Hours ,tostring(floor((a.SecDiff-floor(a.SecDiff/3600)*3600)/60)) AS Minutes ,tostring(a.SecDiff-floor(a.SecDiff/3600)*3600-floor((a.SecDiff-floor(a.SecDiff/3600)*3600)/60)*60) AS Seconds FROM ( SELECT YourUniqueIDField, SecondDiff(YourDateTime1,YourDateTime2) AS SecDiff FROM YourTable ) a ) b