Find the midpoint of 2 datetime

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

SQL Server 2005 Forums

Transact-SQL (2005)

Find the midpoint of 2 datetime

Author

Topic

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-09-30 : 04:30:57

Guys.

I have 2 dates:
StartDate : 2008-09-30 08:00:00
EndDate: 2008-09-30 16:00:00.

How shall write an SQL statement to find the mid-point of these 2 dates?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 04:33:42

[code]SELECT DATEADD(ss,DATEDIFF(ss,@StartDate,@EndDate)/2,@StartDate)[/code]

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-09-30 : 04:40:14

Thanks visakh16. It is Good.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 05:43:51

quote:
Originally posted by liangtp@hotmail.com

Thanks visakh16. It is Good.

cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 05:54:38

For small intervals, just beware of integer division.
Maybe you should use milliseconds instead?

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '2008-09-30 08:00:00',
	@EndDate = '2008-09-30 16:00:00'

SELECT	DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, @StartDate, @EndDate) / 2, @StartDate)

And with small interval I refer to seconds

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '2008-09-30 08:00:00',
	@EndDate = '2008-09-30 08:00:03'

SELECT	DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, @StartDate, @EndDate) / 2, @StartDate)
--SELECT DATEADD(ss,DATEDIFF(ss,@StartDate,@EndDate)/2,@StartDate)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources