Cannot SUM datetime field in SQL Server migrated from Access

Question

I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to SUM the column but I need to change to a Time or numeric data type due to 8117# errors when I SUM. SQL Server will not allow a conversion using the designer / ALTER commands as there are always implicit/explicit conversion errors. Thank you for any possible solutions you may have to offer.

• James Z: Remember that time datatype in SQL Server is supposed to used for storing time part, not duration. It cannot handle over 24 hours.

Answer 1

Presumably your new column has values which are a certain datetime relative to 1-Jan-1900, or some other date.

...in which case, you could rename your column, and create a calculated column with your original column name, which is the number of second (or minutes or whatever granularity you want), so that you can SUM that instead.

Example:

SELECT t = DATEADD(SECOND, SUM(DATEDIFF(SECOND, '19000101', myTimeColumn)), '19000101')
FROM dbo.someTable;

• AlienV: This makes sense. Thanks for your help. I will try it.