CODEWITHSUNDEEP
sql-servervb.netreporting-servicesssrs-2012
Julaayi
Julaayi

Reputation: 499

Convert hh:mm to minutes in SQL Server

I have data coming from a source system which sends time in hh:mm nvarchar format. I am trying to convert it into minutes. I only want to do this in SSRS Report as per the need. So, I am looking for a SSRS Expression rather than T-SQL.

Here is how my data looks like:

10:00
-2:00
-11:30

Expected Output:

600
-120
-690

Here is what I tried so far but looking for a better/performance efficient way

SUM(CInt(LEFT(Replace(Fields!HHMM.Value, "-", ""),InStrRev(Replace(Fields!HHMM.Value, "-", ""),":")-1))*60 + 
CInt(RIGHT(Replace(Fields!HHMM.Value, "-", ""),InStrRev(Replace(Fields!HHMM.Value, "-", ""),":")))
)

Thanks!

Upvotes: 2

Views: 448

Answers (5)

Julaayi
Julaayi

Reputation: 499

Here is what that worked in my case which also handles (null) values in SSRS.

IIF(Left(Fields!TIME.Value, 1) = "-", "-", "") &  
Right("000" & CInt(Replace(LEFT(Replace(Fields!TIME.Value, "-", ""), (INSTR(Replace(Fields!TIME.Value, "-", ""), ":"))), ":", "")) * 60 + 

CInt(IIF(IsNumeric(MID(Fields!TIME.Value, INSTR(Fields!TIME.Value, ":") + 1, 10)), 
                    MID(Fields!TIME.Value, INSTR(Fields!TIME.Value, ":") + 1, 10), 
                    Nothing
        ))
, 3)

Upvotes: 1

Zanyar Jalal
Zanyar Jalal

Reputation: 1864

You can use Scalar function 

CREATE FUNCTION fn_To_Min
(
    @timeVar nvarchar(10)
)
RETURNS int
AS
BEGIN

    declare @op char(1) = SUBSTRING(@timeVar, 1, 1)
    if(@op = '-')
    begin
        set @timeVar = SUBSTRING(@timeVar, 2, LEN(@timeVar))
    end
    else
        set @op = ''

    declare @time time = CONVERT( TIME, @timeVar )

    declare @h int = DATEPART(hh, @time)
    declare @m int = DATEPART(Minute, @time)

    RETURN CONVERT(int, CONCAT(@op, (@h * 60) + @m))

END

Call

SELECT fn_To_Min('10:25'), col1, col2, ...

Upvotes: 1

Hannover Fist
Hannover Fist

Reputation: 10860

Here's something similar to what forpas did in SSRS syntax.

=LEFT(Fields!TIME.Value, INSTR(Fields!TIME.Value, ":") - 1) * 60 + 
    IIF(LEFT(Fields!TIME.Value, 1) = "-", -1, 1) * 
    MID(Fields!TIME.Value, INSTR(Fields!TIME.Value, ":") + 1, 10)

Upvotes: 2

gwt
gwt

Reputation: 2413

This can do what you want:

In TSQL:

select (case when CONVERT(integer,Replace('-11:30',':','')) < 0 then -1 else 1 end ) * ((DATEPART(HOUR,CONVERT(Datetime,Replace('-11:30','-',''))) * 60) + DATEPART(MINUTE,CONVERT(Datetime,Replace('-11:30','-',''))))

EDIT:

You can translate the TSQL Query I posted to something like this to use it in SSRS (please note that I did not test the SSRS code):

Switch(Cint(Replace(Fields!timeField.Value,":","")) < 0,-1,Cint(Replace(Fields!timeField.Value,":","")) >= 0,1) * ((Hour(cdate(Replace(Fields!timeField.Value,":",""))) * 60 ) + Minute(cdate(Replace(Fields!timeField.Value,":",""))))

Upvotes: 1

forpas
forpas

Reputation: 164079

With string functions and casting to integer:

select
  cast(left(timecol, charindex(':', timecol) - 1) as int) * 60 +
  case when left(timecol, 1) = '-' then -1 else 1 end *
  cast(substring(timecol, charindex(':', timecol) + 1, len(timecol)) as int)
from tablename

or without casting by implicit conversions to integer:

select
  left(timecol, charindex(':', timecol) - 1) * 60 +
  case when left(timecol, 1) = '-' then -1 else 1 end *
  substring(timecol, charindex(':', timecol) + 1, len(timecol))
from tablename

Replace timecol with the actual name of the column.
See the demo.
Results:

 600
-120
-690

Upvotes: 1

Related Questions