3

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.

1
  • 1
    Remember that time datatype in SQL Server is supposed to used for storing time part, not duration. It cannot handle over 24 hours. – James Z Mar 1 '15 at 8:45
4

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;
1
  • This makes sense. Thanks for your help. I will try it. – AlienV Mar 16 '15 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.