CODEWITHSUNDEEP
sql-server-2008t-sqluser-defined-functions
kidwispa
kidwispa

Reputation: 25

scalar function for date formatting

I have a column which contains time in minutes. What I am looking to do is create a function that returns the following formats:

0 = NULL
>0 and <60 mins = '##min'
between 60 mins & 1440 mins = '##hr,##min'
>1440mins = '##days,##hr,##min'

I've managed to get some of this done using the function below however I'm struggling with how to split days, hours and minutes:

CREATE FUNCTION dbo.udf_Format_Date_Friendly 
(
    -- Add the parameters for the function here
    @minutes int
)
RETURNS nvarchar(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result nvarchar(100)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = CASE WHEN @minutes = 0 THEN ''
            WHEN @minutes <= 60 THEN CAST(@minutes AS nvarchar(100)) + ' min' 
          WHEN @minutes BETWEEN 60 AND 1440 THEN CAST(CAST(@minutes/60 AS INT) AS nvarchar(100)) + ' hr'
           ELSE NULL end

    -- Return the result of the function
    RETURN @Result

END
GO

Upvotes: 2

Views: 207

Answers (2)

Praveen
Praveen

Reputation: 9355

Try;

CREATE FUNCTION dbo.udf_Format_Date_Friendly (@minutes int)
RETURNS nvarchar(100)
AS
BEGIN
   return CASE 
    when @minutes < 60 then cast( @minutes as varchar(10)) + 'min'
    when @minutes < 1400 then cast(@minutes/60 as varchar(10)) + 'hr, ' + cast(@minutes%60 as varchar(10)) + 'min'
    else cast(@minutes/(1400) as varchar(10)) + 'days, ' + cast((@minutes%1400)/60 as varchar(10)) + 'hr, ' + cast(((@minutes%1400)%60) as varchar(10)) + 'min'
    end
end
go

Demo

;with data(d) as (
    select 0 d union all
    select 45 d union all
    select 80 union all
    select 1800
)
select d, dbo.udf_Format_Date_Friendly(d) val
from data

OutPut

0   0min
45  45min
80  1hr, 20min
1800    1days, 6hr, 40min

Upvotes: 1

AHiggins
AHiggins

Reputation: 7219

A piece-by-piece approach might be a bit more readable than the current "one expression to do everything" format - the code below will build up the expression one piece at a time, starting with the largest (days) and going down to the minute level. 

CREATE FUNCTION dbo.udf_Format_Date_Friendly 
(
    -- Add the parameters for the function here
    @minutes int
)
RETURNS nvarchar(100)
AS
BEGIN

    -- Declare the return variable here
    DECLARE @Result nvarchar(10) = ''


    IF @minutes > 1440 
      BEGIN
        SET @Result = @Result + CAST(@minutes/1440 AS NVARCHAR(10)) + ' days, '
      END

    IF @minutes > 60 
      BEGIN
        SET @Result = @Result + CAST((@minutes%1440) /60 AS NVARCHAR(10)) + ' hr, '
      END

    IF @minutes > 0
      BEGIN
        SET @Result = @Result + CAST(@minutes % 60 AS NVARCHAR(10)) + ' min'
      END

    -- Return the result of the function
    RETURN @Result

END
GO

Upvotes: 0

Related Questions