Jiansen Lu
Sql Server function to determine a leap year
June 30th 2008 04:34
Sql Server function to determine a leap year
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?
Solution
Yes, there a few rules to consider when determining when a year is a leap year. For instance, contrary to popular belief not all years divisible by 4 are leap years. For instance, the year 1900 was not a leap year. However, you needn't bother yourself about leap year rules... you can let the SQL Server engine do the work for you!
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
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
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.
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?
Solution
Yes, there a few rules to consider when determining when a year is a leap year. For instance, contrary to popular belief not all years divisible by 4 are leap years. For instance, the year 1900 was not a leap year. However, you needn't bother yourself about leap year rules... you can let the SQL Server engine do the work for you!
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
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
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.
| 55 |
| Vote |
Add To: 
subscribe to this blog
