Append 0 - SQL Server Forums

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)

Append 0

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-10-20 : 06:01:09

A SQL Server DB table has a column named Duration whose datatype is varchar. Assume that 3 records under this column are (in hours & minutes) 25:30, 31:30, 16:00. The sum of these 3 records should give me 73:00 i.e. 73 hours & 00 minutes. To find the sum i.e. the total duration, this is the query I am using:

---------------------------
SELECT CAST(CAST((SUM(CAST(SUBSTRING(Duration, 0, CHARINDEX(':',Duration)) AS INT))*60 +
SUM(CAST(RIGHT(Duration,2) AS INT)))/60 AS VARCHAR(10)) + ':' + CAST((SUM(CAST(RIGHT(Duration,2) AS INT)))%60 AS VARCHAR(10)) AS VARCHAR(10)) AS TotalDuration
FROM ETS
---------------------------

The above query gives me the correct result but the problem is if the 'minutes' value comes to 0, then the resultset generated is 73:0 & not 73:00. How do I append a 0 at the end under such circumstances?

Thanks,

Ron

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 06:22:22

Díd you see the answer made here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112441
at 10/14/2008 : 06:40:01 ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:25:24

[code]declare @test table
(
id int,
val varchar(5)
)

insert into @test
select 1,'25:30' union all
select 2, '31:35' union all
select 3, '16:00'

select cast(sum(left(val,charindex(':',val)-1)*1)+sum(substring(val,charindex(':',val)+1,len(val))*1)/60 as varchar(2))
+':' + left(cast(sum(substring(val,charindex(':',val)+1,len(val))*1)%60 as varchar(2)) + '00',2)
from @test

output
-----
73:50[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-20 : 09:26:03

quote:

Originally posted by visakh16

declare @test table
(
id int,
val varchar(5)
)



insert into @test
select 1,'25:30' union all
select 2, '31:35' union all
select 3, '16:00'


select cast(sum(left(val,charindex(':',val)-1)*1)+sum(substring(val,charindex(':',val)+1,len(val))*1)/60  as varchar(2))
+':' + right('0'+cast(sum(substring(val,charindex(':',val)+1,len(val))*1)%60  as varchar(2)),2)from @test

output
-----
73:50

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-20 : 09:33:45

[code]declare @test table
(
id int,
val varchar(5)
)

insert @test
select 1, '25:30' union all
select 2, '31:35' union all
select 3, '16:00'

-- Visakh16
select cast(sum(left(val,charindex(':',val)-1)*1)+sum(substring(val,charindex(':',val)+1,
len(val))*1)/60 as varchar(2)) +':' + left(cast(sum(substring(val,charindex(':',val)+1,
len(val))*1)%60 as varchar(2)) + '00',2)
from @test

-- Madhi
select cast(sum(left(val,charindex(':',val)-1)*1)+sum(substring(val,charindex(':',val)+1,
len(val))*1)/60 as varchar(2)) +':' + right('0'+cast(sum(substring(val,charindex(':',val)+1,
len(val))*1)%60 as varchar(2)),2)
from @test

-- Peso 1 (generic version)
SELECT STUFF(CONVERT(CHAR(5), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(CASE CHARINDEX(':', val) WHEN 0 THEN 0 ELSE 3600 * LEFT(val, CHARINDEX(':', val) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', val) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(val, CHARINDEX(':', val) + 1, 2) END)) AS theMinutes
FROM @Test
) AS d

-- Peso 2 (specialized version)
SELECT STUFF(CONVERT(CHAR(5), DATEADD(SECOND, theSeconds, '19000101'), 8), 1, 2, CAST(theSeconds / 3600 AS VARCHAR(12)))
FROM (
SELECT SUM(3600 * LEFT(val, 2) + 60 * RIGHT(val, 2)) AS theSeconds
FROM @Test
) AS d[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-20 : 09:36:59

One more

select ltrim(hour_part+minute_part/60)+':'+right('0'+ltrim(minute_part%60),2) from
(
select 
	sum(parsename(replace(val,':','.'),2)*1) as hour_part,
	sum(parsename(replace(val,':','.'),1)*1) minute_part
from @test
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources