SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.HiLoDate
(	
	@date1 datetime,
	@date2 datetime,
	@date3 datetime,
	@date4 datetime
)
RETURNS TABLE 
AS
RETURN 
(
	with cte (xDate) as (	
							select @date1
							union all
							select @date2
							union all
							select @date3
							union all
							select @date4
							)

	select min(xDate) as LoDate, max(xDate) as HiDate
	from 
				
)
GO

/* TEST

select * 
from dbo.HiLoDate('3/1/2012','3/15/2011','2/1/2012','11/1/2011')

*/

CREATE FUNCTION [dbo].[HiLoDate2]
(	
	@date1 datetime,
	@date2 datetime,
	@date3 datetime,
	@date4 datetime
)
RETURNS TABLE 
AS
RETURN 
(
with cte (max1,max2, min1,min2 ) as 
	(	select  case when @date1 >= @date2 then @date1 else @date2 end
			   ,case when @date3 >= @date4 then @date3 else @date4 end
			   ,case when @date1 <= @date2 then @date1 else @date2 end   
			   ,case when @date3 <= @date4 then @date3 else @date4 end
			   )
	
select  case when min1 <= min2 then min1 else min2 end as LoDate			   					
	   ,case when max1 >= max2 then max1 else max2 end as HiDate			   					
from cte
				
)

GO

/* TEST -- compare executions plans

select * 
from dbo.HiLoDate('3/1/2012','3/15/2011','2/1/2012','11/1/2011')


select * 
from dbo.HiLoDate2('3/1/2012','3/15/2011','2/1/2012','11/1/2011')

*/

----===== Declare some obviously named variables
--DECLARE @NumberOfRows INT,
--        @StartDate    DATETIME,
--        @EndDate      DATETIME,
--        @Days         INT --This is still the "range"
--;
----===== Preset the variables to known values
-- SELECT @NumberOfRows = 1000000,
--        @StartDate    = '2010', --Inclusive
--        @EndDate      = '2020', --Exclusive
--        @Days         = DATEDIFF(dd,@StartDate,@EndDate)
--;
----===== Create "random constrained whole dates" within 
--     -- the parameters identified in the variables above.
     
-- SELECT TOP (@NumberOfRows)
--        SomeRandomDate1 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate,
--        SomeRandomDate2 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate,
--        SomeRandomDate3 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate,
--        SomeRandomDate4 = ABS(CHECKSUM(NEWID())) % @Days + @StartDate
--INTO #Test
--   FROM sys.all_columns ac1
--  CROSS JOIN sys.all_columns ac2
--;
PRINT 'New Test'
DECLARE @MAX	datetime,
		@MIN	datetime
DECLARE @Date datetime2

SET @Date = SYSDATETIME()
SELECT @MAX = CASE WHEN SomeRandomDate1 >= SomeRandomDate2
					AND SomeRandomDate1 >= SomeRandomDate3
					AND SomeRandomDate1 >= SomeRandomDate4 THEN SomeRandomDate1
				   WHEN SomeRandomDate2 >= SomeRandomDate3
					AND SomeRandomDate2 >= SomeRandomDate4 THEN SomeRandomDate2
				   WHEN SomeRandomDate3 >= SomeRandomDate4 THEN SomeRandomDate3 ELSE SomeRandomDate4 END,
	@MIN = CASE WHEN SomeRandomDate1 <= SomeRandomDate2
					AND SomeRandomDate1 <= SomeRandomDate3
					AND SomeRandomDate1 <= SomeRandomDate4 THEN SomeRandomDate1
				   WHEN SomeRandomDate2 <= SomeRandomDate3
					AND SomeRandomDate2 <= SomeRandomDate4 THEN SomeRandomDate2
				   WHEN SomeRandomDate3 <= SomeRandomDate4 THEN SomeRandomDate3 ELSE SomeRandomDate4 END
FROM #Test

PRINT DATEDIFF( ms, @Date, SYSDATETIME())
SET @Date = SYSDATETIME()

SELECT @MAX = HiDate, @MIN = LoDate
FROM #Test t
CROSS APPLY dbo.HiLoDate(SomeRandomDate1, SomeRandomDate2,SomeRandomDate3,SomeRandomDate4) HiLoDate

PRINT DATEDIFF( ms, @Date, SYSDATETIME())
SET @Date = SYSDATETIME()

SELECT @MAX = HiDate, @MIN = LoDate
FROM #Test t
CROSS APPLY dbo.HiLoDate2(SomeRandomDate1, SomeRandomDate2,SomeRandomDate3,SomeRandomDate4) HiLoDate

PRINT DATEDIFF( ms, @Date, SYSDATETIME())
GO 5

New Test
457 --Direct comparison
606 --HiLoDate
547 --HiLoDate2
New Test
459 --Direct comparison
584 --HiLoDate
540 --HiLoDate2
New Test
466 --Direct comparison
592 --HiLoDate
539 --HiLoDate2
New Test
453 --Direct comparison
590 --HiLoDate
555 --HiLoDate2
New Test
459 --Direct comparison
585 --HiLoDate
545 --HiLoDate2