SQL, how to get the midpoint between two given dates?

Question

I have two dates:

2012-10-04 12:48:56:000 and 2012-10-04 12:48:58:000

Expected Result - 2012-10-04 12:48:57:000

---

2012-10-04 12:48:56:000 and 2012-10-04 12:48:56:010

Expected Result - 2012-10-04 12:48:56:005

(dates are fictitious, because in sql server the DATETIME data type is increased by 3 in milliseconds)

Answer 1

With your dates ...

 SELECT DATEADD(ms, DATEDIFF(ms,'2012-10-04 12:48:56:000', '2012-10-04 12:48:58:000')/2, '2012-10-04 12:48:56:000') 

Answer 2

Something like that:

 with sample_data (start_dt, end_dt) as ( select cast('2012-10-04 12:48:56:000' as datetime), cast('2012-10-04 12:48:58:000' as datetime) union all select cast('2012-10-04 12:48:56:000' as datetime), cast('2012-10-04 12:48:56:010' as datetime) ) select start_dt, end_dt, dateadd(millisecond, datediff(millisecond, start_dt, end_dt) / 2, start_dt) from sample_data 

Although the second pair is not calculated properly. Probably due to a resolution of 3 milliseconds.

Answer 3

 declare @date1 datetime; declare @date2 datetime; set @date1 = '2012-10-04 12:48:56:000'; set @date2 = '2012-10-04 12:48:58:000'; select DateAdd(ms, DateDiff(ms, @date1, @date2)/2, @date1) 

Answer 4

 -- let day d1 and d2 are DateTime variables (d1 < d2) -- get the differnce in milliseconds -- (you can change it but be careful with oveflow situations) declare @diff integer = datediff (ms, @d1, @d2) -- the middle is the first date + half of the difference declare @middle DateTime = dateadd (ms, @diff / 2, @d1) 

Answer 5

Try this (you can replace part of the date depending on how exactly you want to be):

 DateAdd(ms, DateDiff(ms, date1, date2), date1)/2