SQL Sum NVARCHAR(50) Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Good morning team,

 I have an error (The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.) when trying to sum hours and minutes (duration column) from a table where the column data type is nvarchar(50). I need to aggregate the hours+minutes so I can join on another table to get total work hours for installers out in the field. The select query below will work for specific CustomerName however, I receive the error attached when running for all customers. I've tried at least 10 different ways to resolve but the error is always the same.

As always, thank you all for your time and expertise in helping me get this resolved!

SELECT     TOP (100) PERCENT CustomerName, CONVERT(varchar(10), SUM(DATEDIFF(minute, 0, Duration)) / 60) + '.' + CONVERT(varchar(10), SUM(DATEDIFF(minute, 0,
                      Duration)) % 60) AS install_hours_worked
FROM         dbo.vWIP_Install_TimeSheets AS T
GROUP BY CustomerName


Here is the source query for the view above. This is simply to refine to a list of time sheet entries that meet certain service item criteria (in this case, we're only looking for a WIP report on installation jobs).

SELECT     TOP (100) PERCENT CustomerName, CustomerId, Duration
FROM         dbo.TimeTracking
WHERE     (CustomerName IN
                          (SELECT     Job
                            FROM          dbo.vWIP_Install)) AND (ServiceItemName IN
                          (SELECT     FullName
                            FROM          dbo.vWip_Items
                            WHERE      (dbo.TimeTracking.Duration > '0.00') AND (CustomFields = 'Install')))
ORDER BY CustomerName
Sum-error-tracking-table-source.png
Sum-error_view.png