Monday, July 7, 2008

SQL Server Function to Determine a Leap Year

The following scalar function takes in a year and returns a bit flag indicating whether the passed in year is a leap year or not.

create function dbo.fn_IsLeapYear (@year int)
returns bit
return(select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))
when 2 then 1
else 0

That's all there is to it! The function takes in the year, appends '0228' to it (for February 28th) and adds a day. If the month of the next day is a 2 (as extracted by the DATEPART function), then we're still in February so it must be a leap year! If not, it is not a leap year.
Here are a few examples:

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?'

No comments: