CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user1942281
user1942281

Reputation: 41

Subtracting only Time from Datetime columns

I have two columns of one table as @ABC as DateTime and @xyz as DateTime – Datatype.

I want to subtract only time and I am trying like this:

For ABC = 21/02/2012 6:10:00 PM and XYZ = 01/01/2001 12:00:00 AM -> 1st Row.

CONVERT(varchar(10), dbo.checkingtime.ABC – dbo.checkingtime.XYZ, 108)

and I am getting the result as 18:10, but I want the result as 05:50 in Hours and Minutes only.

is it possible ? ? ?

Upvotes: 4

Views: 3354

Answers (4)

Art
Art

Reputation: 5792

Here's good example I found:

Select start_date, end_date, time_diff,
   EXTRACT(DAY FROM time_diff) days,
   EXTRACT(HOUR FROM time_diff) hours,
   EXTRACT(MINUTE FROM time_diff) minutes,
   EXTRACT(SECOND FROM time_diff) seconds
From
(
Select start_date, end_date, end_date - start_date time_diff
From
(
Select CAST(to_date('21/02/2012 06:10:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP)  end_date
 , CAST(to_date('01/01/2012 12:00:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP) start_date
From dual
))
/

Upvotes: 0

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

This will do, 

select right(date2-date1,7) as time from table1

Result:

TIME
5:50AM

SQL_LIVE_DEMO

Upvotes: 0

András Ottó
András Ottó

Reputation: 7695

You can try a function like this:

CREATE FUNCTION GetTimeDifference
(
@FirstDate datetime,
@SecondDate datetime
)
RETURNS varchar(10)
AS
BEGIN
DECLARE @Difference INT
DECLARE @FirstTimeInMin INT
DECLARE @SecondTimeInMin INT

SELECT @FirstTimeInMin = 
(DATEPART(hour,@FirstDate) * 60 + DATEPART(minute,@FirstDate))
SELECT @SecondTimeInMin =
(DATEPART(hour,@SecondDate) * 60 + DATEPART(minute,@SecondDate))

IF @FirstTimeInMin = 0
SET @FirstTimeInMin = 24 * 60

IF @SecondTimeInMin = 0
SET @SecondTimeInMin = 24 * 60

SET @Difference = @FirstTimeInMin - @SecondTimeInMin

IF(@Difference < 0)
SET @Difference = @Difference * -1

RETURN RIGHT('0' + CONVERT(varchar(10), @Difference / 60), 2)
 + ':' + 
RIGHT('0' + CONVERT(varchar(10), @Difference - (@Difference / 60) * 60 ), 2)

END
GO

And you can use it like this:

SELECT dbo.GetTimeDifference('02/02/2012 6:10:00 PM','01/01/2001 12:00:00 AM')

It should results 05:50

Upvotes: 0

Gustav Klimt
Gustav Klimt

Reputation: 440

This is prolly a thing you want. This is built in function, but i would advise you to build your own. 

DATEDIFF ( datepart , startdate , enddate )

Take note, that you will need to 'mask' start and end date with same day to get results you want (and that is time diference). The result will be in mins, but you can format it with ease to hour:min.

cheers

Upvotes: 1

Related Questions