debugsqlMinor
Cannot SUM datetime field in SQL Server migrated from Access
Viewed 0 times
cannotfieldsqlmigratedsumserverfromaccessdatetime
Problem
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.Solution
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:
...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;Code Snippets
SELECT t = DATEADD(SECOND, SUM(DATEDIFF(SECOND, '19000101', myTimeColumn)), '19000101')
FROM dbo.someTable;Context
StackExchange Database Administrators Q#94099, answer score: 4
Revisions (0)
No revisions yet.