# Background

Helping one of my peers get a new set of reports up.

She currently displays the time spent per each task in minutes.  I wanted to display that in english rather than just computer numbers — 67, 90, 145.

These numbers are in minutes, by the way.

# Template

Muhammad Imran is one blogger that handles tasks that others stumbled over in the simplest, most graceful ways.

I am dumbfounded and wonder, are you kidding me….

# Code

Here are scaler and inline table functions that I customized for our usage.

Functions

1. Scaler
• [dbo].[sayDuration]
2. Inline Table Value Function
• [dbo].[ITVF_sayDuration]

## [dbo].[sayDuration]

```
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[sayDuration]') is null
begin

exec( '
create function [dbo].[sayDuration]()
returns varchar(255)
as
begin
return( select 1/0 as [shell])
end
'
)

end
go

ALTER function [dbo].[sayDuration]
(
@duration       int
)
RETURNS varchar(255)
AS
begin

/*

Duration
in minutes

*/
declare @durationAsDatetime datetime
declare @durationInWords    varchar(100)

-- convert duration to seconds and then datetime
set @durationAsDatetime
= DateAdd(s, @duration * 60,'1900-01-01'  )
- '1900-01-01'

set @durationInWords
=
--Days
case
when DateDiff(day,'1900-01-01',@durationAsDatetime) > 0 then

Convert(varchar(10),DateDiff(day,'1900-01-01',@durationAsDatetime))
+ ' Day(s) '
else ''
end

-- Hours
+ case
when DatePart(hh,@durationAsDatetime) > 0 then
Convert(varchar(10),DatePart(hh,(@durationAsDatetime)))
+ ' Hour(s) '
else ''
end

-- minutes
+ case
when DatePart(mi,@durationAsDatetime) > 0 then
Convert(varchar(10),DatePart(mi,(@durationAsDatetime)))
+ ' Minute(s) '
else ''
end

return (@durationInWords)

end
go

grant execute on [dbo].[sayDuration] to [public]
go

```

### Sample

```
print [dbo].[sayDuration](67)

print [dbo].[sayDuration](270)

```

## [dbo].[ITVF_sayDuration]

```SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[ITVF_sayDuration]') is null
begin

exec( '
create function [dbo].[ITVF_sayDuration]()
returns table
as return( select 1/0 as [shell])
'
)

end
go

ALTER function [dbo].[ITVF_sayDuration]
(
@duration       int
)
RETURNS TABLE
AS
return
(
/*

Duration
in minutes

*/
with cte
as
(
select

--duration in miuntes
[duration] = @duration

-- convert duration to seconds and then datetime
, [durationAsDatetime]
= DateAdd(s, @duration * 60,'1900-01-01'  )
- '1900-01-01'

)

select
[duration]
= [duration]

, [durationInWords]
=
--Days
case
when DateDiff(day,'1900-01-01',[durationAsDatetime]) > 0 then

Convert(varchar(10),DateDiff(day,'1900-01-01',[durationAsDatetime]))
+ ' Day(s) '
else ''
end

-- Hours
+ case
when DatePart(hh,[durationAsDatetime]) > 0 then
Convert(varchar(10),DatePart(hh,([durationAsDatetime])))
+ ' Hour(s) '
else ''
end

-- Minutes
+ case
when DatePart(mi,[durationAsDatetime]) > 0 then
Convert(varchar(10),DatePart(mi,([durationAsDatetime])))
+ ' Minute(s) '
else ''
end

from cte

)

go

grant select on [dbo].[ITVF_sayDuration] to [public]
go

```

### Sample

```
declare @tblDuration TABLE
(
[dateBegin]   datetime
, [dateEnd]     datetime
, [duration]
as datediff(minute, dateBegin, dateEnd)
)

insert into @tblDuration
(
[dateBegin]
, [dateEnd]
)
select '2016-04-01 09:00', '2016-04-04 17:00'
union all
select '2016-04-01 09:00', '2016-04-16 17:45'
select
durationInMinutes
= tblD.[duration]
, tblDL.durationInWords
from   @tblDuration tblD
cross apply [dbo].[ITVF_sayDuration](duration) tblDL
;

```

# References

1. SQL SERVER – Shorter way to convert Seconds to Days, Hours, Minutes, Seconds
April 10, 2013 by Muhammad Imran
https://raresql.com/2013/04/10/sql-server-shorter-way-to-convert-seconds-to-days-hours-minutes-seconds/