Transact SQL – Say Duration in Words

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….

MuhammedImran

 

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)

Output

Scaler-Sample

 

[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
;



Output

sayDuration

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/

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s