sum the hours in varchar datatype values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sum the hours in varchar datatype values

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-11-19 : 08:28:59

I have a table DATA and column is TOTALHOURS(varchar datatype) column contain values as 15:30:02,05:17:08'......., i need to SUM these values a.

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 08:32:52

[code]DECLARE @Sample TABLE
(
dt VARCHAR(20)
)

INSERT @Sample
SELECT '15:30:02' UNION ALL
SELECT '05:17:08'

SELECT DATEADD(SECOND, SUM(DATEDIFF(SECOND, 0, dt)), 0)
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-11-19 : 08:38:24

Thanks Peso

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 08:38:29

[code]DECLARE @Sample TABLE
(
dt VARCHAR(20)
)

INSERT @Sample
SELECT '15:30:02' UNION ALL
SELECT '05:17:08'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theSeconds, '19000101'), 8), 1, 2, CAST(theSeconds / 3600 AS VARCHAR(12)))
FROM (
SELECT SUM(DATEDIFF(SECOND, 0, dt)) AS theSeconds
FROM @Sample
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources