How to Handle DATEDIFF(MINUTE, '00:00', '24:20') Like scenario?

Question

There is a column in my Table. In which we are storing string value in format 'HH:MM'.During fetching records with this table every things works ok with

DATEDIFF(MINUTE, '00:00', ColumnName)

Problem is when we have Value greater than 23:59. Its showing error like

Conversion failed when converting date and/or time from character string.

Can anybody suggest me the right approach for achieving this scenario.

Answer 1

So it sounds like your saving the length of a time period. Try storing it in minutes. My query can handle numbers of different lengths since it's based on the colon.

DECLARE @yourTable TABLE (ColumnName VARCHAR(10));
INSERT INTO @yourTable
VALUES  ('100:00'),
        ('24:20');

SELECT  ColumnName,
        (hr * 60) + minut AS time_period_in_minutes
FROM @yourTable
CROSS APPLY (SELECT CAST(SUBSTRING(ColumnName,0,CHARINDEX(':',ColumnName)) AS INT),
                    CAST(SUBSTRING(ColumnName,CHARINDEX(':',ColumnName) + 1,LEN(ColumnName)) AS INT)) CA(hr,minut)

Results:

ColumnName time_period_in_minutes
---------- ----------------------
100:00     6000
24:20      1460

Answer 2

Try to this

select DATEDIFF(MINUTE, '00:00', case when ISDATE(ColumnName)=0 then '00:00' else ColumnName end )

Answer 3

If you are storing the value as something other than a time, why not just store the number of minutes and convert to whatever format you want on output?

Otherwise, I would suggest that you simply convert the value to minutes:

select (cast(left(ColumnName, 2) as int) * 60 +
        cast(right(ColumnName, 2) as int)
       ) as Minutes

If you are not using date/time values, there is no requirement for using the functions specifically designed for them.

EDIT:

To handle hours longer than 99, use charindex():

select (cast(left(ColumnName, charindex(':', ColumnName) - 1) as int) * 60 +
        cast(right(ColumnName, 2) as int)
       ) as Minutes