Problem
I need to write a function to determine if particular year is a leap year (i.e. February contains 29 days rather than 28 days). I know that there are various rules for calculating leap years. Is there an easy way to figure this out? Can you provide an example or two to validate various years?
Solution1
create function dbo.fn_IsLeapYear (@year int)
returns bit
as
begin
    return(select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime))) 
    when 2 then 1 
    else 0 
    end)
end
go

select dbo.fn_IsLeapYear(1900) as 'IsLeapYear?'
select dbo.fn_IsLeapYear(2000) as 'IsLeapYear?'
select dbo.fn_IsLeapYear(2007) as 'IsLeapYear?'
select dbo.fn_IsLeapYear(2008) as 'IsLeapYear?'
Solution2
if year modulo 400 is 0 then leap
else if year modulo 100 is 0 then no_leap
else if year modulo 4 is 0 then leap
else no_leap
there for you function doesn't work well -> select dbo.fn_IsLeapYear(1582) as sql server拼接字符串函数'IsLeapYear?' 
What about this:
create function dbo.IsLeapYear(@theyear int)
returns tinyint
as
begin
    declare @retVal tinyint
    select @retVal =
    case
        when @theyear % 4 <> 0
        then 0
    else
        case
            when @theyear % 100 = 0
            then
        case
            when @theyear % 400 = 0
            then 1
            else 0 end
            else 1 end
    end
    return @retVal
end
go
select dbo.IsLeapYear(1582) as LeapYear_YES_NO
drop function dbo.IsLeapYear
Solution3
DROP FUNCTION dbo.fn_IsLeapYear
go
CREATE FUNCTION dbo.fn_IsLeapYear (@year INT)
RETURNS BIT
AS
BEGIN
    DECLARE @fourYearRule INT
    DECLARE @oneHundredYearRule INT
    DECLARE @fourHundredYearRule INT
    SET @fourYearRule = @year % 4
    SET @oneHundredYearRule = @year % 100
    SET @fourHundredYearRule = @year % 400
    IF (@fourYearRule = 0 AND (@oneHundredYearRule <> 0 AND @fourHundredYearRule <> 0))
        RETURN 1
    IF (@fourYearRule = 0 AND @oneHundredYearRule = 0 AND @fourHundredYearRule = 0)
        RETURN 1
RETURN 0
END
GO

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。