Coverting to hours

[robert030975]

HI

Ive been trying to convert values to hours and minutes and failing badly!

4.25 would be 4.15
4.5 would be 4:30
4.75 would be 4:45


Help Please!

 

[robert030975]

DROP Table #rd1

CREATE Table #rd1 (

[numdate] decimal (6,2)

);
Insert Into #rd1 Values('4.25');
Insert Into #rd1 Values('4.5');
Insert Into #rd1 Values('4.75');
Insert Into #rd1 Values('4.0');


select convert(varchar, convert(int,floor(numdate)))+':'+
replicate('0',(2 - len(convert (varchar, convert(int,(numdate - floor(numdate )) * 60.0)))))
+convert (varchar, convert(int,(numdate - floor(numdate )) * 60.0))
FROM #rd1


yippie do I get a gold star

 

[djj55]

yippie do I get a gold star

Hey robert030975 please try and use the TGML markup for code.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

What about this:

Select Convert(varchar(5), DateAdd(minute, numdate*60, 0), 108) 
From #rd1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom