Using Teradata SQL: Find the difference between two timestamps and express as hours

It's often useful to compare two timestamps in SQL and express the resulting difference in hours. You might need to do this for systems that monitor rotas or workng schedules.

The code below does this for you, showing the result with 2 decimal places. Obviously you can change this by adjusting the outer CAST statement.

(
    CAST ((
        (CAST((CAST(end_ts AS DATE)- CAST(start_ts AS DATE)) AS DECIMAL(18,6)) * 24)
          + ((EXTRACT(  HOUR FROM end_ts) - EXTRACT(  HOUR FROM start_ts)))
          + ((EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts)) / 60.000000)
          + ((EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts)) / 3600.000000)
      ) AS DECIMAL (6,2) )
  ) AS hours_diff

Source: Teradata Community Post by Dieter (dnoeth)

"Our greatest glory is not in never failing, but in rising every time we fall." - Confucius (551–479 BC)