How to split minutes into days, hours and minutes in tsql

sql-server tsql datetime date-conversion
cihadakt picture cihadakt · May 3, 2013 · Viewed 17.2k times · Source

I have a column which consist of minutes. Is there any simple way to split minutes column into one column which shows days, hours, minutes only?

DURATION              
-----------
67           ==> 1 hour, 7 minutes
1507         ==> 1 day, 1 hour, 7 minutes
23           ==> 23 minutes

I googled for solution but I didn't find any solution similar for me. I want to show this in a report but trying to find how can I solve this column looks meaningfully. Duration column's calculation like this. 

avg(datediff(MINUTE, ei.SentDate, eo.SentDate)) over(partition by ei.mailbox) as DURATION

Answer

Vishwanath Dalvi picture Vishwanath Dalvi · May 3, 2013

A google search landed me here http://www.sqlservercentral.com/Forums/Topic490411-8-1.aspx

and It says... which I just tested working ...

Declare @theMinutes int
Set @theMinutes = 67 

Select @theMinutes / 1440 as NoDays  -- 1440 minutes per day 
       , (@theMinutes % 1440) / 60 as NoHours -- modulo 1440 
       , (@theMinutes % 60) as NoMinutes -- modulo 60

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
Sql Server string to date conversion

I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I …

Read More