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

Jeremy Poisson
Jeremy Poisson used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
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 PoissonPresident

Author

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 Analyst

Commented:
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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Jeremy PoissonPresident

Author

Commented:
Thanks Icohan - can you give me an example of a CTE or temp table query?
Database Analyst
Commented:
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

Jeremy PoissonPresident

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial