Click here to Skip to main content
15,868,016 members
Home / Discussions / Database
   

Database

 
QuestionRe: split datatable column using c# Pin
Eddy Vluggen5-Aug-14 2:35
professionalEddy Vluggen5-Aug-14 2:35 
AnswerRe: split datatable column using c# Pin
Jaimin H. Soni6-Aug-14 1:12
Jaimin H. Soni6-Aug-14 1:12 
GeneralRe: split datatable column using c# Pin
Eddy Vluggen6-Aug-14 1:56
professionalEddy Vluggen6-Aug-14 1:56 
GeneralRe: split datatable column using c# Pin
Jaimin H. Soni6-Aug-14 2:00
Jaimin H. Soni6-Aug-14 2:00 
GeneralRe: split datatable column using c# Pin
Eddy Vluggen6-Aug-14 2:30
professionalEddy Vluggen6-Aug-14 2:30 
QuestionHelp with XML Pin
byka31-Jul-14 6:36
byka31-Jul-14 6:36 
GeneralRe: Help with XML Pin
PIEBALDconsult31-Jul-14 7:01
mvePIEBALDconsult31-Jul-14 7:01 
GeneralRe: Help with XML Pin
byka31-Jul-14 7:04
byka31-Jul-14 7:04 
AnswerRe: Help with XML Pin
Richard Deeming31-Jul-14 7:29
mveRichard Deeming31-Jul-14 7:29 
QuestionCannot create a foreign key reference (probably my fault) Pin
Richard MacCutchan31-Jul-14 0:38
mveRichard MacCutchan31-Jul-14 0:38 
AnswerRe: Cannot create a foreign key reference (probably my fault) Pin
Simon_Whale31-Jul-14 1:27
Simon_Whale31-Jul-14 1:27 
GeneralRe: Cannot create a foreign key reference (probably my fault) Pin
Richard MacCutchan31-Jul-14 1:36
mveRichard MacCutchan31-Jul-14 1:36 
AnswerRe: Cannot create a foreign key reference (probably my fault) Pin
Eddy Vluggen31-Jul-14 1:28
professionalEddy Vluggen31-Jul-14 1:28 
GeneralRe: Cannot create a foreign key reference (probably my fault) Pin
Richard MacCutchan31-Jul-14 1:39
mveRichard MacCutchan31-Jul-14 1:39 
GeneralRe: Cannot create a foreign key reference (probably my fault) Pin
Eddy Vluggen31-Jul-14 11:08
professionalEddy Vluggen31-Jul-14 11:08 
QuestionInsert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results ) Pin
LTMKH30-Jul-14 22:30
LTMKH30-Jul-14 22:30 
AnswerRe: Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results ) Pin
Mycroft Holmes30-Jul-14 22:51
professionalMycroft Holmes30-Jul-14 22:51 
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.

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.