SQL how to retrieve the middle point 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 is
2012-10-04 12:48:57:000

---

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

Expected result is
2012-10-04 12:48:56:005

(the dates are fictional, since in sql server the millisecond part DATETIME datatype is increasing by 3 )

Answer 1

With your own 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 this:

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 doesn't compute properly. Probably because of the 3 milliseconds resolution.