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
Jeremy PoissonPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Please try run a select on that table and use ISDATE() function that will reveal any exisitng rows that have non datetime/bad data that needs to be fixed first - typical to datetime stored into nvarchar or varchar with no (or poor) data validation .

https://database.guide/isdate-examples-in-sql-server/
Jeremy PoissonPresidentAuthor Commented:
Hi Icohan - I ran the query and have 619 rows out of 137,422 in the TimeTracking table that meet this criteria. How do I omit these so the aggregate can continue?
lcohanDatabase AnalystCommented:
Assuming they have  a row identifier(PK,  or some ID unique index)  you could put all those ID's in a CTE or a #temp table and then use EXCEPT or NOT EXISTS to exclude those rows from aggregations until data is fixed.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Jeremy PoissonPresidentAuthor Commented:
Thanks Icohan - can you give me an example of a CTE or temp table query?
lcohanDatabase AnalystCommented:
my apologies for the delay - so sorry...so would be something like the code below where the "record_id" must be replaced by the column(s) that identifies the rows having that bad datetime

--with CTE
with ts_list as
(select record_id from dbo.vWIP_Install_TimeSheets where ISDATE(Duration)=0)

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 
WHERE NOT EXISTS (SELECT record_id from ts_list where record_id = T.record_id)

--with #table
select record_id into #ts_list from dbo.vWIP_Install_TimeSheets where ISDATE(Duration)=0;

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 
WHERE NOT EXISTS (SELECT record_id from #ts_list where record_id = T.record_id)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeremy PoissonPresidentAuthor Commented:
Thank you VERY MUCH! The error has subsided and the data is correct.

Awesome job - I appreciate all of the time, effort and expertise; well done.

Jeremy
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.