Hi,

I want to display Days Hours Mins Format.

I am Having two columns Like below,

Col1 (in days)    col2 (In Hours : Mins)
3days                   4:5 

In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5

from this 31.5 i should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.

Kindly please help me in this,

thanks in ad

If you want to display it in days itself you just need this i guess

SELECT Col1 + LEFT(Col2,CHARINDEX(':',col2 + ':')-1) + ' Hours ' + CAST(LEFT(STUFF(col2,1,CHARINDEX(':',col2 + ':'),'') + '00',2) * 0.6 AS varchar(10)) + ' Mins'
FROM Table

Hi ,

Thanks for your reply,

i am getting below error in this

SELECT  --LEFT(Col2,CHARINDEX(':',col2 + ':')-1) + ' Hours ' +''+ 
CAST(LEFT(STUFF(col2,1,CHARINDEX(':',col2 + ':'),'') + '00',2) * 0.6 AS varchar(10))-- + ' Mins'
FROM cte

Hi Vikash,

Thanks for your help,

i cant directly take col1 as days . i should convert days as hours (days*9) for certain scenarios

please find my below scenario , in that the total hours crossed 9 hours so it should add in days value

id	sub id	Days	Hours	
abz	1	2	null	
abz	2	null	3	
abz	3	3	4	
abz	4	null	null	
abz	5	2	null	
abz	6	1	5	
abz	7	null	2	
abz	8	null	null	
abz	9	2	3	
abz	10	null	2.5	
		10	19.5	
				
	output is 	12days 	1hour	30 mins

DECLARE @T TABLE
(
ID VARCHAR(99),
SubID INT,
Days INT,
Hours FLOAT
)
;
INSERT INTO @T VALUES
('abz',	    1,	2,	null)	
,('abz',	2,	null,	3	)
,('abz',	3,	3,	4	)
,('abz',	4,	null,	null	)
,('abz',	5,	2,	null	)
,('abz',	6,	1,	5	)
,('abz',	7,	null,	2	)
,('abz',	8,	null,	null	)
,('abz',	9,	2,	3	)
,('abz',	10,	null,	2.5	);

SELECT SUM(DAYS)+FLOOR(SUM(HOURS)/9) DAYS
	   , FLOOR(SUM(HOURS)-FLOOR(SUM(HOURS)/9)*9) AS HOURS
	   , (SUM(HOURS)-FLOOR(SUM(HOURS)))*60 MINUTES FROM @T 

/*
DAYS	HOURS	MINUTES
12	     1	     30
*/

Hi Eric,

Thanks for your reply,

I found some other way and worked based on it, 

I converted days and hours into minutes . 


id	subid	days	   hours 
1	a		 7			null
1	b		null		1:15
1	c		 3			null
1	d		null		0:30

so the total days 10 ,hours 1, mins 75


10 days calculation is (10*9) ===>5400 mins
1 Hours calculation is (1*60) ====>60  mins
75 mins						  ====>75  mins

so the total mins 5535


declare @time int = 5535

select  CAST(@time/540 AS  VARCHAR)  ' Days ' ,
 CAST( (@time%540)/60 AS   VARCHAR)  ' Hours ',
CAST(  (@time%60) AS  VARCHAR)  ' Minutes'