Hours stored as varchar format

[andyiain]

Hi,

I've got me a database where the developers included a total hours column as a varchar format. Specifically it's of the form: hours colon minutes e.g. 8:30

I need to add up these hours and so after a lot of messing around a searching this forum I found this method:

SUM(Datediff(hh,0,CONVERT(DATETIME,totalnoofhours,114)))

However I've now found some values for totalnoofhours of 24:00
or 26:30 etc. In these cases I get an out-of-range datetime error when I try to sum the hours.

Short of changing the format of the data (which I am trying to sort out through the supplier) can anyone suggest any short term solutions.

Kind regards
Andy

 

[bborissov]

DECLARE @Test TABLE (totalnoofhours varchar(200))
INSERT INTO @Test VALUES ('8:30')
INSERT INTO @Test VALUES ('8:30')
INSERT INTO @Test VALUES ('24:30')


SELECT SUM(CAST(SUBSTRING(totalnoofhours,1,CHARINDEX(':',totalnoofhours)-1) as int)+
           CAST(SUBSTRING(totalnoofhours,CHARINDEX(':',totalnoofhours)+1,2) as int)*1.0/60)
FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[andyiain]

Thanks for that, worked a treat.

I've now found a few cases not in the format hours colon month but I think I can pretty easily remove these before running any queries.