How to handle DATEDIFF (MINUTE, '00: 00 ', '24: 20') Like the script?

Question

There is a column in my table. In which we save the string value in the format 'HH:MM' . When retrieving records in this table, everything works fine with

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

The problem is that we have a value greater than 23:59 . His error shown, for example

Conversion error while converting date and / or time from character string.

Can anyone suggest me the right approach to achieve this scenario.

Answer 1

So, it looks like you are saving the duration of a period of time. Try to save it in minutes. My request can process numbers of different lengths, since it is based on a 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

If you save the value as something other than time, why not just save the number of minutes and convert to whatever format you want in the output?

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

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

If you do not use date and time values, there is no need to use functions specially designed for them.

EDIT:

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

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

Answer 3

try it

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