Convert Number to Hours and Minutes in SQL Server

In one of my previous posts http://www.sqlservercurry.com/2009/03/convert-integer-to-date-and-time-in-sql.html, I had demonstrated how to convert integer to Date and Time. I got a mail from one of my readers who asked me if I could calculate hours and seconds from an integer field.

Here's how to do so:

DROP Table #SampleTable

CREATE Table #SampleTable (

[numasdate] int

);

 

Insert Into #SampleTable Values('9436');

Insert Into #SampleTable Values('537');

Insert Into #SampleTable Values('9323');

Insert Into #SampleTable Values('12525');

 

-- To calculate Total Hours and Minutes

Select numasdate,

Cast(numasdate / 60 as Varchar) + ' hours ' +

Cast(numasdate % 60 as Varchar) + ' minutes'

as [TotalHoursAndMinutes]

From

#SampleTable

Output:

9436    157 hours 16 minutes

537        8 hours 57 minutes

9323    155 hours 23 minutes

12525    208 hours 45 minutes