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
s 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
WHERE (CustomerName IN
FROM dbo.vWIP_Install)) AND (ServiceItemName IN
> '0.00') AND (CustomFields = 'Install')))
ORDER BY CustomerName