Search code examples
sqltimesumvarcharnvarchar

SUM time in varchar/nvarchar data type field

I have troubles to SUM time in a varchar/nvarchar data type field.

This is the format what I'm trying to SUM: (HH:mm:ss)

Solution

  • I have figured it out:

    select convert(decimal(18, 2), convert(decimal(18, 2), ppt.hour) + convert(decimal(18, 2), convert(decimal(18, 2), ppt.minute/ 60)) + convert(decimal(18, 2), convert(decimal(18, 2), ppt.second / 3600))) AS Hours, ppt.[column] from (select SUM(convert(decimal(2),left([column], 2))) AS hour, SUM(convert(decimal(2), left(right([column], 5), 2))) AS minute, SUM(convert(decimal(2), right([column], 2))) AS second, [column] FROM [table] GROUP BY [column]) AS ppt