Here's one way, as inspired by Anatoly Lubarsky.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fn_IsLeapYear (@dtDate DATETIME)
RETURNS BIT
AS
BEGIN
DECLARE @dtFeb28 DATETIME,
@nMonthDay tinyint
--
-- Add a day to the 28th of February and see if it is the 1st or the 29th.
SELECT @dtFeb28 = CONVERT(VARCHAR(4), YEAR(@dtDate)) + '0228',
@nMonthDay = DATEPART(DAY, DATEADD(DAY, 1, @dtFeb28))
IF (@nMonthDay = 29)
RETURN 1
RETURN 0
-- Usage:
-- SELECT dbo.fn_IsLeapYear (GETDATE())
-- SELECT dbo.fn_IsLeapYear (dateadd(year,1,GETDATE()))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO