Difference between 2 times in sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Difference between 2 times in sql

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-05-11 : 15:51:47

Hi,

I need to get the difference between 2 times.
How can i get this?

something like:

select (time2-time1)/60 as time from mytable

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-11 : 15:56:20

DATEDIFF?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-05-11 : 16:06:59

Lamprey,

It would be same day but different times.
Would datediff work?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-11 : 16:12:26

yes. see here: http://msdn.microsoft.com/en-us/library/ms189794.aspx

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-05-11 : 16:39:42

Thanks lamprey and russell.
It worked.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-11 : 21:28:17

All lamprey, I just showed the link to what he was saying

Please do post back when you have more questions.

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-05-12 : 12:17:35

Hi one more question on the same topic:
I do not see decimals in here.
Here is my query:

select datediff(minute, StartTime, EndTime)/60 as RevTime from my_tbl where mdate between '04/04/2011' and '04/08/2011'

The problem here is :
if i use hour in datediff, i am not getting decimal values.
If i use minute in datediff, and divide it by 60, even then i am not getting decimals.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-05-12 : 12:40:29

When you divide by 60, you are using integer math, which does not produce decimal results.

To get decimal results, you need to divide by a decimal number.


select
	HoursFromMinutesDiff		=
		convert(numeric(6,2),round(datediff(mi,0,D2-D1)/60.0000,2)),
	HoursFromSecondsDiff		=
		convert(numeric(6,2),round(datediff(ss,0,D2-D1)/3600.0000,2)),
	HoursFromMillisecondsDiff	=
		convert(numeric(6,2),round(datediff(ms,0,D2-D1)/3600000.0000,2)),
	a.*
from
	( -- Test Data
	select D1 = convert(datetime,'20110510 14:43:59.993'), D2 =getdate()
	) a

HoursFromMinutesDiff HoursFromSecondsDiff HoursFromMillisecondsDiff D1                      D2
-------------------- -------------------- ------------------------- ----------------------- -----------------------
               45.87                45.88                     45.88 2011-05-10 14:43:59.993 2011-05-12 12:36:34.227

CODO ERGO SUM

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-05-13 : 12:41:22

Thanks Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources