Convert time format DD.HH:MM:SS to total number of minutes in SQL

Question

I have a database with the time it takes to process an order and I need to convert it to the total number of minutes in SQL.

I get how to do this with the HH:MM:SS format using

LTRIM(DATEDIFF(MINUTE, 0, ProcessTime))

However whenever the time goes past a day the format becomes DD.HH:MM:SS

The "." after the D is what's throwing me off and it's returning an error when I try to run the code.

[Time is not saved with display format](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028). What is the data type of your column? — Zohar Peled, Aug 06 '15 at 18:07

score 1 · Answer 1 · answered Aug 06 '15 at 18:11

Assuming the values are stored as strings (as that is the only way it would be possible to store DD.HH:MM:SS)

WITH T(D) AS
(
SELECT '04.12:07:59' UNION ALL
SELECT '12:07:59'
)
SELECT D,
       CAST(LEFT(D,LEN(D)-8) AS float) * 24 * 60 + DATEDIFF(MINUTE, 0, RIGHT(D,8)) AS Mins
FROM T

Returns

+-------------+------+
|      D      | Mins |
+-------------+------+
| 04.12:07:59 | 6487 |
| 12:07:59    |  727 |
+-------------+------+

Convert time format DD.HH:MM:SS to total number of minutes in SQL

1 Answers1