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

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