|
/**********************************************************
EarlierOf.sql
**********************************************************/
/**********************************************************
Return the later of two DATETIME variables.
Parameter 1: DATETIME1
Parameter 2: DATETIME2
Works for a variety of DATETIME or NULL values. Even
though comparisons with NULL are actually indeterminate, we
know conceptually that NULL is not earlier
or later than any other date provided.
SYNTAX:
SELECT dbo.EarlierOf('1/1/2000','12/1/2009')
SELECT dbo.EarlierOf('2009-12-01 00:00:00.000','2009-12-01 00:00:00.521')
SELECT dbo.EarlierOf('11/15/2000',NULL)
SELECT dbo.EarlierOf(NULL,'1/15/2004')
SELECT dbo.EarlierOf(NULL,NULL)
**********************************************************/
USE AdventureWorks
GO
IF EXISTS
(SELECT *
FROM sysobjects
WHERE name = 'EarlierOf'
AND xtype = 'FN'
)
BEGIN
DROP FUNCTION EarlierOf
END
GO
CREATE FUNCTION EarlierOf (
@Date1 DATETIME,
@Date2 DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ReturnDate DATETIME
IF (@Date1 IS NULL AND @Date2 IS NULL)
BEGIN
SET @ReturnDate = NULL
GOTO EndOfFunction
END
ELSE IF (@Date1 IS NULL AND @Date2 IS NOT NULL)
BEGIN
SET @ReturnDate = @Date2
GOTO EndOfFunction
END
ELSE IF (@Date1 IS NOT NULL AND @Date2 IS NULL)
BEGIN
SET @ReturnDate = @Date1
GOTO EndOfFunction
END
ELSE
BEGIN
SET @ReturnDate = @Date1
IF @Date2 < @Date1
SET @ReturnDate = @Date2
GOTO EndOfFunction
END
EndOfFunction:
RETURN @ReturnDate
END -- End Function
GO
---- Set Permissions
--GRANT SELECT ON EarlierOf TO UserRole1
--GRANT SELECT ON EarlierOf TO UserRole2
--GO
|