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
 Script Library
 Datetime Range Midpoint Function

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-07-09 : 17:17:16
This function, F_DATETIME_RANGE_MIDPOINT, computes the midpoint in time between two input parameter datetime values, @start and @end, and returns the midpoint as a datetime. The midpoint result is accurate to the nearest SQL Server clock tick of 1/300 of a second, with any remainder rounded up to the next whole tick.

The two input datetime parameters can be any valid datatimes. If either or both input parameters are null, the function returns a null.

The script creates the function, and runs a demo script to show the expected results. I have also included the results.

I wrote this function as a general solution to the problem of finding the mean of two datetime values that was posted in this thread:
How to display mean datetime with aggregates?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67968




if objectproperty(object_id('dbo.F_DATETIME_RANGE_MIDPOINT'),'IsScalarFunction') = 1
	begin drop function dbo.F_DATETIME_RANGE_MIDPOINT end
go
create function dbo.F_DATETIME_RANGE_MIDPOINT
	( @start datetime, @end datetime 	)
returns datetime
as
/*
Function: F_DATETIME_RANGE_MIDPOINT

	Computes the midpoint in time between two input
	parameter datetime values, @start and @end, and
	returns the midpoint as a datetime.

	The midpoint result is accurate to the nearest clock tick of 1/300
	of a second, with any remainder rounded up to the next whole tick.

	This function is valid for the entire range of datetime
	from 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997

	This function returns the same result, no matter what order
	the input parameters are passed.

	This function returns null if either or both input parameters are null.

	Usage:
	select	Midpoint =
		dbo.F_DATETIME_RANGE_MIDPOINT('20060101', '20060102')

*/
begin

declare @st datetime
declare @et datetime

declare @hours int
declare @ms int
declare @ticks int
declare @ms_half int

-- Set order of input parameters so that return value is always
-- the same no matter what order the input values are passed.
if @start > @end
	select @st = @end, @et = @start
else
	select @st = @start, @et = @end

-- Get hours boundary difference.
-- Subtract 1 from diff, before dividing by 2 and multiplying by 2
-- so the milliseconds remaining is always positive and
-- hours is always >= zero and evenly divisible by 2.
set @hours = ((datediff(hh,@st,@et)-1)/2)*2

-- Get remainder milliseconds
set @ms = datediff(ms,0,@et-dateadd(hh,@hours,@st))

-- Convert  remainder milliseconds to
-- SQL Server 'clock ticks' of 1/300 of a second
set @ticks = ((@ms/10)*3) + ((@ms%10)/3)

-- Divide 'clock ticks' by 2, 
-- and round up ( 9/2 = 4.5 , so round up to 5)
-- to follow the usual rounding rules
set @ticks = (@ticks/2) + (@ticks%2)

-- Convert clock ticks back to milliseconds
select @ms_half = ((@ticks/3)*10) + floor(((@ticks%3)*3.5))

-- Compute midpoint as:
-- Add ( hours boundary difference / 2 ) hours to start time
-- Then add (remainder milliseconds / 2 ) to result of prior step
return dateadd(ms,@ms_half,dateadd(hh,@hours/2,@st))

end
go

select
	Midpoint = 
	convert(varchar(23),dbo.F_DATETIME_RANGE_MIDPOINT( a.ST, a.ET ) ,121) ,
	[Start] = convert(varchar(23),a.ST ,121) ,
	[End] = convert(varchar(23),a.ET ,121) ,
	a.Comment
from
(
select ST = getdate(), ET = getdate()+2  , Comment = 'Now thru 2 days from now' union all
select '20060101', '20060102' , 'One day diff' union all
select '20060102', '20060101' , 'One day diff reversed' union all
select '20060102 23:59:59.997', '20060103 23:59:59.997' ,'One day diff'  union all
select '20060102', '20060103 00:00:00.020' ,'One day + 6 ticks'  union all
select '20060102', '20060103 00:00:00.017' ,'One day + 5 ticks'  union all
select '20060102', '20060103 00:00:00.013' ,'One day + 4 ticks'  union all
select '20060102', '20060103 00:00:00.010' ,'One day + 3 ticks'  union all
select '20060102', '20060103 00:00:00.007' ,'One day + 2 ticks'  union all
select '20060102', '20060103 00:00:00.003' ,'One day + 1 tick'  union all
select '20060102', '20060103 00:00:00.000' ,'One day + 0 tick'  union all
select '20060102', '20060102 23:59:59.997' ,'One day - 1 tick'  union all
select '20060102', '20060102 23:59:59.993' ,'One day - 2 ticks'  union all
select '20060102', '20060102 23:59:59.990' ,'One day - 3 ticks'  union all
select '20060102', '20060102 23:59:59.987' ,'One day - 4 ticks'  union all
select '20060102', '20060102 23:59:59.983' ,'One day - 5 ticks'  union all
select '20060102', '20060102 23:59:59.980' ,'One day - 6 ticks'  union all
select '20030101', '20030101' ,'Both times same'  union all
select '20030101', '20030108' ,'One week diff'  union all
select '20021228', '20030104' ,'One week diff'  union all
select '20010701', '20010713' ,'12 day diff'  union all
select '20010701', '20010714' ,'13 day diff'  union all
select '20010630', '20010713' ,'13 day diff'  union all
select '19901201 01:00:00.000', '19901201  02:00:00.000' ,'1 hour diff'  union all
select '19901201 01:00:33.003', '19901201  02:00:33.003' ,'1 hour diff'  union all
select '19901201 01:00:00.000', '19901201  01:30:00.000' ,'30 min diff'  union all
select '19901201 01:00:33.447', '19901201  01:30:33.447' ,'30 min diff'  union all
select '19901201 01:00:00.000', '19901201  01:05:00.000' ,'5 min diff'  union all
select '19901201 01:00:29.123', '19901201  01:05:29.123' ,'5 min diff'  union all
select '19901201 01:00:00.000', '19901201  01:01:00.000' ,'1 min diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:01.000' ,'1 sec diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.100' ,'100 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.050' ,'50 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.023' ,'23 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.020' ,'20 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.013' ,'13 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.010' ,'10 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.007' ,'7 ms diff'  union all
select '19901201 01:00:00.000', '19901201  01:00:00.003' ,'3 ms diff'  union all
select '20030101', '20030201' ,'One month diff 31 days'  union all
select '20030101', '20030201' ,'One month diff 30 days'  union all
select '20040201', '20040301' ,'One month diff 29 days'  union all
select '20030201', '20030301' ,'One month diff 28 days'  union all
select '20030101', '20040101' ,'One year diff'  union all
select '20040101', '20050101' ,'One year diff leap year'  union all
select '20050101', '20070101' ,'Two year diff'  union all
select '20040101', '20060101' ,'Two year diff with leap year 1st'  union all
select '20030101', '20050101' ,'Two year diff with leap year 2nd'  union all
select '20060101', '20060301' ,'2 month diff'  union all
select '20040101', '20040301' ,'2 month diff leap year'  union all
select null, '20060101' ,'Start time null'  union all
select '20060102', null ,'End time null'  union all
select null, null ,'Both null'  union all
select '17530101', '99991231 23:59:59.997' ,'Max datetime diff'  union all
select  '99991231 23:59:59.997','17530101' ,'Max datetime diff reversed'
) a

Results:

Midpoint                Start                   End                     Comment                          
----------------------- ----------------------- ----------------------- -------------------------------- 
2006-07-10 17:12:24.300 2006-07-09 17:12:24.300 2006-07-11 17:12:24.300 Now thru 2 days from now
2006-01-01 12:00:00.000 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 One day diff
2006-01-01 12:00:00.000 2006-01-02 00:00:00.000 2006-01-01 00:00:00.000 One day diff reversed
2006-01-03 11:59:59.997 2006-01-02 23:59:59.997 2006-01-03 23:59:59.997 One day diff
2006-01-02 12:00:00.010 2006-01-02 00:00:00.000 2006-01-03 00:00:00.020 One day + 6 ticks
2006-01-02 12:00:00.010 2006-01-02 00:00:00.000 2006-01-03 00:00:00.017 One day + 5 ticks
2006-01-02 12:00:00.007 2006-01-02 00:00:00.000 2006-01-03 00:00:00.013 One day + 4 ticks
2006-01-02 12:00:00.007 2006-01-02 00:00:00.000 2006-01-03 00:00:00.010 One day + 3 ticks
2006-01-02 12:00:00.003 2006-01-02 00:00:00.000 2006-01-03 00:00:00.007 One day + 2 ticks
2006-01-02 12:00:00.003 2006-01-02 00:00:00.000 2006-01-03 00:00:00.003 One day + 1 tick
2006-01-02 12:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000 One day + 0 tick
2006-01-02 12:00:00.000 2006-01-02 00:00:00.000 2006-01-02 23:59:59.997 One day - 1 tick
2006-01-02 11:59:59.997 2006-01-02 00:00:00.000 2006-01-02 23:59:59.993 One day - 2 ticks
2006-01-02 11:59:59.997 2006-01-02 00:00:00.000 2006-01-02 23:59:59.990 One day - 3 ticks
2006-01-02 11:59:59.993 2006-01-02 00:00:00.000 2006-01-02 23:59:59.987 One day - 4 ticks
2006-01-02 11:59:59.993 2006-01-02 00:00:00.000 2006-01-02 23:59:59.983 One day - 5 ticks
2006-01-02 11:59:59.990 2006-01-02 00:00:00.000 2006-01-02 23:59:59.980 One day - 6 ticks
2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 Both times same
2003-01-04 12:00:00.000 2003-01-01 00:00:00.000 2003-01-08 00:00:00.000 One week diff
2002-12-31 12:00:00.000 2002-12-28 00:00:00.000 2003-01-04 00:00:00.000 One week diff
2001-07-07 00:00:00.000 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 12 day diff
2001-07-07 12:00:00.000 2001-07-01 00:00:00.000 2001-07-14 00:00:00.000 13 day diff
2001-07-06 12:00:00.000 2001-06-30 00:00:00.000 2001-07-13 00:00:00.000 13 day diff
1990-12-01 01:30:00.000 1990-12-01 01:00:00.000 1990-12-01 02:00:00.000 1 hour diff
1990-12-01 01:30:33.003 1990-12-01 01:00:33.003 1990-12-01 02:00:33.003 1 hour diff
1990-12-01 01:15:00.000 1990-12-01 01:00:00.000 1990-12-01 01:30:00.000 30 min diff
1990-12-01 01:15:33.447 1990-12-01 01:00:33.447 1990-12-01 01:30:33.447 30 min diff
1990-12-01 01:02:30.000 1990-12-01 01:00:00.000 1990-12-01 01:05:00.000 5 min diff
1990-12-01 01:02:59.123 1990-12-01 01:00:29.123 1990-12-01 01:05:29.123 5 min diff
1990-12-01 01:00:30.000 1990-12-01 01:00:00.000 1990-12-01 01:01:00.000 1 min diff
1990-12-01 01:00:00.500 1990-12-01 01:00:00.000 1990-12-01 01:00:01.000 1 sec diff
1990-12-01 01:00:00.050 1990-12-01 01:00:00.000 1990-12-01 01:00:00.100 100 ms diff
1990-12-01 01:00:00.027 1990-12-01 01:00:00.000 1990-12-01 01:00:00.050 50 ms diff
1990-12-01 01:00:00.013 1990-12-01 01:00:00.000 1990-12-01 01:00:00.023 23 ms diff
1990-12-01 01:00:00.010 1990-12-01 01:00:00.000 1990-12-01 01:00:00.020 20 ms diff
1990-12-01 01:00:00.007 1990-12-01 01:00:00.000 1990-12-01 01:00:00.013 13 ms diff
1990-12-01 01:00:00.007 1990-12-01 01:00:00.000 1990-12-01 01:00:00.010 10 ms diff
1990-12-01 01:00:00.003 1990-12-01 01:00:00.000 1990-12-01 01:00:00.007 7 ms diff
1990-12-01 01:00:00.003 1990-12-01 01:00:00.000 1990-12-01 01:00:00.003 3 ms diff
2003-01-16 12:00:00.000 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 One month diff 31 days
2003-01-16 12:00:00.000 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 One month diff 30 days
2004-02-15 12:00:00.000 2004-02-01 00:00:00.000 2004-03-01 00:00:00.000 One month diff 29 days
2003-02-15 00:00:00.000 2003-02-01 00:00:00.000 2003-03-01 00:00:00.000 One month diff 28 days
2003-07-02 12:00:00.000 2003-01-01 00:00:00.000 2004-01-01 00:00:00.000 One year diff
2004-07-02 00:00:00.000 2004-01-01 00:00:00.000 2005-01-01 00:00:00.000 One year diff leap year
2006-01-01 00:00:00.000 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000 Two year diff
2004-12-31 12:00:00.000 2004-01-01 00:00:00.000 2006-01-01 00:00:00.000 Two year diff with leap year 1st
2004-01-01 12:00:00.000 2003-01-01 00:00:00.000 2005-01-01 00:00:00.000 Two year diff with leap year 2nd
2006-01-30 12:00:00.000 2006-01-01 00:00:00.000 2006-03-01 00:00:00.000 2 month diff
2004-01-31 00:00:00.000 2004-01-01 00:00:00.000 2004-03-01 00:00:00.000 2 month diff leap year
NULL                    NULL                    2006-01-01 00:00:00.000 Start time null
NULL                    2006-01-02 00:00:00.000 NULL                    End time null
NULL                    NULL                    NULL                    Both null
5876-07-02 00:00:00.000 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 Max datetime diff
5876-07-02 00:00:00.000 9999-12-31 23:59:59.997 1753-01-01 00:00:00.000 Max datetime diff reversed

(55 row(s) affected)



CODO ERGO SUM
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site