Click here to Skip to main content
15,860,972 members
Home / Discussions / Database
   

Database

 
GeneralRe: Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results ) Pin
LTMKH31-Jul-14 15:53
LTMKH31-Jul-14 15:53 
GeneralRe: Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results ) Pin
Mycroft Holmes31-Jul-14 16:15
professionalMycroft Holmes31-Jul-14 16:15 
QuestionHow to Calculate Total Time Difference Pin
Robymon28-Jul-14 3:06
Robymon28-Jul-14 3:06 
AnswerRe: How to Calculate Total Time Difference Pin
Corporal Agarn28-Jul-14 3:26
professionalCorporal Agarn28-Jul-14 3:26 
GeneralRe: How to Calculate Total Time Difference Pin
Robymon28-Jul-14 3:36
Robymon28-Jul-14 3:36 
GeneralRe: How to Calculate Total Time Difference Pin
Corporal Agarn28-Jul-14 3:39
professionalCorporal Agarn28-Jul-14 3:39 
AnswerRe: How to Calculate Total Time Difference Pin
David Mujica28-Jul-14 3:28
David Mujica28-Jul-14 3:28 
AnswerRe: How to Calculate Total Time Difference Pin
Andrius Leonavicius28-Jul-14 11:32
professionalAndrius Leonavicius28-Jul-14 11:32 
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:
SQL
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:
SQL
SELECT [dbo].[ufn_GetTimeDifference]('32:10', '26:10') AS 'Time Difference';

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

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

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

-06:00
Regards,
Andrius Leonavicius


modified 30-Jul-14 17:45pm.

GeneralRe: How to Calculate Total Time Difference Pin
Mycroft Holmes29-Jul-14 13:05
professionalMycroft Holmes29-Jul-14 13:05 
GeneralRe: How to Calculate Total Time Difference Pin
Andrius Leonavicius29-Jul-14 14:03
professionalAndrius Leonavicius29-Jul-14 14:03 
GeneralRe: How to Calculate Total Time Difference Pin
Mycroft Holmes29-Jul-14 14:25
professionalMycroft Holmes29-Jul-14 14:25 
GeneralRe: How to Calculate Total Time Difference Pin
Andrius Leonavicius29-Jul-14 14:39
professionalAndrius Leonavicius29-Jul-14 14:39 
AnswerRe: How to Calculate Total Time Difference Pin
Mycroft Holmes29-Jul-14 14:29
professionalMycroft Holmes29-Jul-14 14:29 
AnswerRe: How to Calculate Total Time Difference Pin
Swinkaran4-Aug-14 16:28
professionalSwinkaran4-Aug-14 16:28 
QuestionSQL SERVER DBA--- HELP ME WITH THIS.... Pin
Member 1097517927-Jul-14 20:57
Member 1097517927-Jul-14 20:57 
AnswerRe: SQL SERVER DBA--- HELP ME WITH THIS.... Pin
Tim Carmichael28-Jul-14 2:58
Tim Carmichael28-Jul-14 2:58 
AnswerRe: SQL SERVER DBA--- HELP ME WITH THIS.... Pin
Mycroft Holmes29-Jul-14 14:33
professionalMycroft Holmes29-Jul-14 14:33 
QuestionProblem with SSIS 2012 Pin
amioni24-Jul-14 0:41
amioni24-Jul-14 0:41 
SuggestionRe: Problem with SSIS 2012 Pin
Richard Deeming24-Jul-14 1:57
mveRichard Deeming24-Jul-14 1:57 
GeneralRe: Problem with SSIS 2012 Pin
amioni24-Jul-14 2:04
amioni24-Jul-14 2:04 
QuestionRe: Problem with SSIS 2012 Pin
Richard Deeming24-Jul-14 2:13
mveRichard Deeming24-Jul-14 2:13 
AnswerRe: Problem with SSIS 2012 Pin
amioni24-Jul-14 3:00
amioni24-Jul-14 3:00 
GeneralRe: Problem with SSIS 2012 Pin
Richard Deeming24-Jul-14 3:12
mveRichard Deeming24-Jul-14 3:12 
GeneralRe: Problem with SSIS 2012 Pin
amioni24-Jul-14 3:25
amioni24-Jul-14 3:25 
QuestionRe: Problem with SSIS 2012 Pin
GuyThiebaut24-Jul-14 2:44
professionalGuyThiebaut24-Jul-14 2:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.