CODEWITHSUNDEEP
sqlsybase
Preeti
Preeti

Reputation: 1386

Finding difference between time

In below code I am trying to find time difference in minutes

declare @a time
declare @b time
select @a = "Apr 1, 2014 22:36.000"
select @b = "Apr 2, 2014 02:25.000"
select datediff(minutes,@a,@b)

Expected output is 229 minutes. 24 min (60- 36) + 3 hour (180 min) + 25 min = 229 minutes

But I am getting -1211.This function is doing direct subration. Please help.

Upvotes: 0

Views: 40

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You have declared the variables as time which cannot exceed 24 hours. The date portion is ignored.

In effect you are sending this to the datediff functoon

datediff(minute,'22:36.000', '02:25.000')

and that is why you are getting a negative result.

I suggest you declare @a and @b as datetime instead.

declare @a datetime
declare @b datetime
select @a = "Apr 1, 2014 22:36.000"
select @b = "Apr 2, 2014 02:25.000"
select datediff(minutes,@a,@b)

Upvotes: 2

Ian Kenney
Ian Kenney

Reputation: 6426

you are using time data types rather than datetime data types in your code ( they will ignore the date part and just store the time part.

so its calculating 2:25 - 22:36, ie 20 hours ( 1200 ) plus 11 minutes (all minus because the first time is after the second)

sql server date / time date types are documented here : http://msdn.microsoft.com/en-gb/library/ms186724.aspx

Upvotes: 0

praveen_programmer
praveen_programmer

Reputation: 1062

You can use this query as reference to calculate time difference .

select DATEDIFF(day,2007-11-30,2007-11-20) AS NumberOfDays, DATEDIFF(hour,2007-11-30,2007-11-20) AS NumberOfHours, DATEDIFF(minute,2007-11-30,2007-11-20) AS NumberOfMinutes FROM test_table

Upvotes: 1

Related Questions