How to Calculate Total Time Difference

Robymon

I want to calculate Total Time difference like this eg: 32:10 - 26:10 = 06:00 How to do this with the sql query?

Corporal Agarn

What database are you using? Is 32:10 minutes:seconds? What have you tried?

David Mujica

In MS-SQL you can try

SELECT DATEDIFF(hour, START_DATE,END_DATE) FROM YOURTABLE

Robymon

32:10 means 32 Hours and 10 Minutes, I want to find this Total Hours Difference in Hour and Minutes

Corporal Agarn

What you might want to do is convert the hours to minutes then subtract then convert back.

Andrius Leonavicius

Hi, ---------- Note: I'm making an assumption that time values are given inputs as VARCHARs (data type cannot be changed for some reason). Also, please take a look at Mycroft's comments and answer below. ---------- You can create a user-defined function for this purpose. Here is an example of such a function for SQL Server:

CREATE FUNCTION [dbo].[ufn_GetTimeDifference] (@SecondTime VARCHAR(20),
@FirstTime VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @TotalMinutes INT,
@IsNegative BIT = 0,
@Hours INT,
@Minutes INT,
@TimeDifference VARCHAR(20);

  SET @TotalMinutes = ( ( LEFT(@SecondTime, ( CHARINDEX(':', @SecondTime) - 1 )) \* 60 ) + RIGHT(@SecondTime, 2) ) - 
      ( ( LEFT(@FirstTime, ( CHARINDEX(':', @FirstTime) - 1 )) \* 60 ) + RIGHT(@FirstTime, 2) );

  IF ( @TotalMinutes < 0 )
    BEGIN
        SET @IsNegative = 1;
        SET @TotalMinutes = ABS(@TotalMinutes);
    END

  SET @Hours = ( @TotalMinutes / 60 );
  SET @Minutes = ( @TotalMinutes % 60 );
  SET @TimeDifference = ( CASE
                            WHEN ( @IsNegative = 1 ) THEN '-'
                            ELSE ''
                          END ) + 
						  ( CASE
                              WHEN @Hours < 10 THEN '0' + CAST(@Hours AS VARCHAR(1))
                              ELSE CAST(@Hours AS VARCHAR(20))
                            END ) + 
							':' + 
							( CASE
                              WHEN @Minutes < 10 THEN '0' + CAST(@Minutes AS VARCHAR(1))
                              ELSE CAST(@Minutes AS VARCHAR(2))
                            END );

  RETURN @TimeDifference;

END

GO

As you can see, second time and first time parameters are varchars, which expects time in the HH:MM format. The function returns positive or negative time difference as varchar. Examples of usage:

SELECT [dbo].[ufn_GetTimeDifference]('32:10', '26:10') AS 'Time Difference';

06:00

SELECT [dbo].[ufn_GetTimeDifference]('72:00', '36:33') AS 'Time Difference';

35:27

SELECT [dbo].[ufn_GetTimeDifference]('25:25', '25:25') AS 'Time Difference';

00:00

SELECT [dbo].[ufn_GetTimeDifference](

Mycroft Holmes

This is a bad answer, recommending that someone uses varchar for date function is the WORST thing you can do, globalisation (and crappy data) screws them every time. You should only accept a datetime format and use the databases datetime functionality to deal with it.

Never underestimate the power of human stupidity RAH

Andrius Leonavicius

Hi, Thanks for the reply. First of all, I'm not recommending that someone should use VARCHAR for storing time (date) values. I agree on this part with you. However, I'm looking at this problem from a different perspective than you do. I'm considering the usage of VARCHARs as given inputs (data type cannot be changed for some reason). Let's take a look at OP's example once more: "eg: 32:10 - 26:10 = 06:00". So 32:10 and 26:10 are VARCHARs and they cannot be converted to DATETIME... I made a really bad assumption that OP is aware of DATETIME usage in such situations (as you said) and haven't warned about that.

Regards, Andrius Leonavicius

Mycroft Holmes

Andrius Leonavicius wrote:

I'm considering the usage of VARCHARs as given inputs (which cannot be changed for some reason)

That is a premise I would not accept, if it gets to the database then it must be in a valid format, the OP should be going back to the input and cleaning up the data before it gets into the database. He has more fundamental problems than getting the difference here.

Never underestimate the power of human stupidity RAH

Mycroft Holmes

Robymon wrote:

32:10 - 26:10

As has been pointed out by others this is NOT a valid data format, either change the format to 2 ints (hours and minutes) or convert them to minutes. You can then work on the data using math or datetime functions. While Andrius solution is valid for chopping up strings you should not be dealing with strings in the first place. Fix your underlying data problems and this issue disappears.

Never underestimate the power of human stupidity RAH

Andrius Leonavicius

Well, you're right. +5 for your answer.

Regards, Andrius Leonavicius

Swinkaran

/****** Script for Select the Time difference in HH : MM ******/
SELECT [Id],
CONCAT (DATEDIFF(HOUR ,StartDate, EndDate),
N' : ',
(DATEDIFF(MINUTE,StartDate, EndDate) % 60)) as TimeTaken
FROM [dbo].[YourTable]

Above example, I am first finding the Hour, then finding the minutes. Finally, do a Concatenation to get the results in the format of HH:MM. Hopes this helps.