Free SQL Server Learning

# SQL Server Function to Determine a Leap Year

 By: Armando Prato   |   Read Comments (28)   |   Related Tips: More > Dates

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 bitasbegin    return(select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))     when 2 then 1     else 0     end)endgo

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

As you can see, sometimes you can leverage the SQL Server engine to do some heavy lifting for you!

Next Steps

• Read more about the DATEPART function and its parameters in the SQL Server Books Online
• Read more about the User Defined Function types in the 2000 and 2005 Books Online
• Check out these related tips:

Last Update: 6/25/2008

Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips
Related Resources

 Print Tweet Become a paid author

All comments are reviewed, so stay on subject or we may delete your comment.

Get free SQL tips:

 *Enter Code

 Thursday, October 24, 2013 - 2:55:25 AM - saikumar Read The Tip sorry the above one will not wrk correctly...please refer this.     selectdatepart(YY,'12/31/2013')as "present year", case( (DATEPART(DY, cast(cast('12'as varchar)+'/'+ cast('31' as varchar)+'/'+ cast(datepart(YY,'12/31/2013')as varchar) as datetime)))-(365))   when 0 then 'no leap year' else 'leap year' end AS STATUS     from caw_work_item  where id in(2196,91831)

 Wednesday, October 23, 2013 - 10:52:31 AM - Saikumar Read The Tip leap year by using below...here you can change the column and table name and use it.       select  case( (DATEPART(DY, cast(cast('12'as varchar)+'/'+ cast('31' as varchar)+'/'+ cast(datepart(YY,access_key_expiration_date)as varchar) as datetime)))/365)   when 1 then 'no leap year' else 'leap year' end     from caw_user where id=102587

 Friday, January 23, 2009 - 9:50:58 AM - ScottPletcher Read The Tip Great thread! Maybe this is a slight improvement, to allow a "short-circuit" on the second WHEN condition to avoid the % 400 if not absolutely necessary: RETURN CASE     WHEN ( @year & 3 ) > 0 THEN 0 -- Anything not divisible by 4    WHEN ( @year % 100 ) = 0 AND ( @year % 400 ) <> 0 THEN 0 -- Yr div by 100 but not 400    ELSE 1 -- Anything else isEND

 Tuesday, December 16, 2008 - 12:00:07 AM - Jeff Moden Read The Tip Sure... just doesn't have a thing to do with the subject at hand.

 Monday, December 15, 2008 - 11:25:40 PM - alec.nolan Read The Tip Its a fast and reliable server function which maintenance the file management correctly.Here is a link that might be useful: Energy act of 2005

 Wednesday, December 10, 2008 - 4:55:49 PM - Jeff Moden Read The Tip Heh... and I've always found that I/O problems are resolved by rewriting the code. ;-)  Still, it was a good catch and if you don't think milliseconds matter, process 40 million rows a day.  Thanks again, Bob, and see you back on the "other" forum...

 Wednesday, December 10, 2008 - 1:00:20 PM - Bob Hovious Read The Tip I know, Jeff.   I just couldn't resist the opportunity to get one-up on *you* for a change.    ;) Besides, we probably spent more time on these emails than will ever be saved by the difference in milliseconds.   When something isn't running fast enough, I/O is almost always gonna be the problem.

 Monday, December 08, 2008 - 6:57:43 PM - Jeff Moden Read The Tip I absolutely agree and good catch.  Heh... I got lazy. The real point I was trying to make is that folks didn't need to go through all the gyrations they were going through to check for a Leap Year.

 Monday, December 08, 2008 - 4:59:36 PM - Bob Hovious Read The Tip But CAST or CONVERT are quicker than STR  ;-)                 select isdate(cast(@year as char(4))+'0229')set statistics time on; ;with dateStrings as (select cast(N as char(4)) as xYear  from tally where N between 1000 and 9000)select sum(isdate(xYear+'0229')) from datestrings ;with dateStrings as (select str(N) as xYear  from tally where N between 1000 and 9000)select sum(isdate(xYear+'0229')) from datestrings

 Saturday, October 25, 2008 - 6:29:57 PM - Jeff Moden Read The Tip Not sure why anyone actually needs a function for this... DECLARE @Year INT    SET @Year = 2007 SELECT ISDATE(STR(@Year)+'0229')

 Sunday, July 20, 2008 - 1:10:53 AM - rickyrick Read The Tip Nice tweak!  I guess I should've realized that the second % 400 and that last % 4 were unnecessary.  Good job!  I also wondered after I posted if my test was close enough to yours to merit my claim that it was faster but I didn't want to post again adding unnecessary chat.  So, I'm glad you posted your findings.  Thank you very much...your efforts are highly appreciated. Ricky

 Friday, July 18, 2008 - 12:38:51 AM - steve Read The Tip Hi Ricky, Using your code as-is came in 2nd place on my first test, though it juggled 1st with Tosc's code on subsequent tests.  I was able to squeeze it some more for a guaranteed 1st place as:RETURN CASE WHEN ( @year & 3 ) > 0 THEN 0 -- Anything not divisible by 4 WHEN ( @year % 400 ) = 0 THEN 1 -- Anything divisible by 400 is a leap year WHEN ( @year % 100 ) = 0 THEN 0 -- Anything divisible by 100 is not a leap year ELSE 1 -- Anything else isEND   However as a UDF it still isn't as fast as an in-line statement.  With regards to all the zero-function efforts, the difference came down to how many calculations, comparisons and variable declares/assigns used it seems. Steve.

 Wednesday, July 16, 2008 - 12:00:13 AM - rickyrick Read The Tip Hey Steve,  That was pretty sweet.  Doing the same test you described, I got this little snippet to run in 2092 milliseconds.                 CASE                     WHEN ( @year & 3 ) > 0 THEN 0 -- Anything with 1st or 2nd bits set is not a leap year                    WHEN ( @year % 400 ) = 0 THEN 1 -- Anything divisible by 400 is a leap year                    WHEN ( @year % 100 ) = 0 AND ( @year % 400 ) > 0 THEN 0 -- 100-year rule                     WHEN ( @year % 4 ) = 0 THEN 1                     ELSE 0                   END The trick here is that the case logic returns early if it catches the obvious no-leap-years such as odd numbered years (1st WHEN condition), which are by far the majority of the values tested.  Also, using bitwise operators where possible improves performance as in the first condition above.  One cool thing about this is we can add the logic for the "divisible by 4000" rule or whatever as another WHEN ... THEN ... and the performance shouldn't suffer too bad.  Also, you only need the year so there is no need to deal with a bulky DateTime data type to get the result nor do you need to make any expensive date or cast function calls.  Finally, I prefer this "CASE" construct because you can use it in a SELECT list ( CASE...END As IsLeapYear) or you can simply return the result of the case logic in a UDF without any need of using variables (return CASE...END). I compared the results of this logic with the results of the author's logic and got the exact same results so I'm pretty sure it's accurate.  I wouldn't be surprised if there's a bug in there somewhere though.  I whipped this up and tested it in about an hour at 1 AMish so take it with a grain of salt.   Anyway...my 2 cents.  Ricky

 Tuesday, July 15, 2008 - 11:28:18 PM - tosc Read The Tip Indeed! This is a wonderful thread - increasingly higher-order expectations of Leap-Years :-) Try not to become a man of success but rather to become a man of value. - Albert Einstein So steve, you are my man of value. Cheers.

 Tuesday, July 15, 2008 - 6:16:38 AM - aprato Read The Tip steve, excellent analysis!

 Tuesday, July 15, 2008 - 12:36:55 AM - steve Read The Tip This thread has been interesting to see some solutions for a relatively simple problem.  When writing code I always keep one eye on performance, and as a general rule functions are detrimental to performance. With this in mind I set about testing each of the solutions given by: creating each as a UDF creating a table with all the years from 17753 to 9999 running each function over the table 20 times and summing the time taken in milliseconds The UFD's with fewer function call should be faster than those with more.  And the results I got on my meagre PC box were: tosc (0 functions)                time: 4531 milliseconds rajan (0 functions)               time: 4739 vedmondson (0 functions)    time: 4864 khodges (2 functions)          time: 5905 johnrogerson (3 functions)    time: 92584 aprato (4 functions)             time: 92665 There was a clear correlation between number of functions and time taken, with tosc's solution the fastest of the 0-function group.  However, if we drop the UDF then khodges solution is the simplest to use in-line, ie: "isdate('29 Feb ' + cast(TheYear AS char(4)))" or "isdate('29 Feb ' + TheCYear)" if TheYear is already char (1 function less) and we get: isdate('29 Feb ' + TheCYear)                          time: 2195 isdate('29 Feb ' + cast(TheYear AS char(4)))    time: 2589   I do agree that it's better to use SQL Server's grunt, but not if it takes all day.  If using a UDF then rolling your own seems to be the best idea, though using isdate() - even with the cast() - is simple enough that having a UDF seems irrelevant.  Anyway, just my 2 cents.  :)  S.

 Monday, July 14, 2008 - 10:00:43 PM - Rajan Read The Tip Create Function dbo.Fn_IsLeapYear(@Year Bigint)Returns BitASBeginDeclare @R4 Int,        @R100 Int,        @R400 Int,        @IsLeap BitSet @R4 = @Year % 4Set @R100= @Year % 100Set @R400= @Year % 400Set @IsLeap = 0if (@R4 = 0 )  Begin   if (@R400 = 0)       Set @ISLeap=1   else if (@R100 <>0)          Set @ISLeap=1  EndReturn @IsLeapEndSelect .dbo.Fn_IsLeapYear(2002)Select .dbo.Fn_IsLeapYear(2100)Select .dbo.Fn_IsLeapYear(2000) I think this would be better solution for finding the year as Leap or Not Cheers,  S.Rajan

 Monday, July 14, 2008 - 8:11:53 AM - ScottPletcher Read The Tip I like tosc's code.  Should be efficient.  No need to go thru too many gyrations just to check a leap yr. [Although I agree that 16th century dates are not really relevant :-) ].

 Monday, July 14, 2008 - 6:50:38 AM - aprato Read The Tip Kristen.... this looks to me like it won't compile; you may want to double check the posted code However, yes, you could use isdate() as well.  The bottom line:  Let the engine work for you.

 Monday, July 14, 2008 - 6:45:37 AM - aprato Read The Tip There are various ways of "skinning a cat" as they say. The take away..... let the engine do the work for you!

 Monday, July 14, 2008 - 6:18:21 AM - John Rogerson Read The Tip I've re-invented this wheel several times in various languages, but what works best for me is to subtract a day from March 1st and then see what day value is returned for February.  It seems to cope with the century/millennium rules. And yes, I acknowledge that it is very similar to the original post but my excuse is that I've been using this approach for many years. CREATE FUNCTION fn_IsLeapYear (@year SMALLINT)RETURNS BITAS BEGIN    RETURN CASE DAY((                     SELECT DATEADD (day, - 1, CAST( @year AS CHAR (4)) + '-03-01')                    ))             WHEN 29 THEN 1             ELSE 0           END    END

 Monday, July 14, 2008 - 4:15:26 AM - VEdmondson Read The Tip This is the code I use to calculate a leap year, its much more efficient when doing the calculation over several hundred thousand rows...  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 INTSET @fourYearRule = @year % 4 SET @oneHundredYearRule = @year % 100SET @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 0END GO

 Monday, July 14, 2008 - 1:33:42 AM - KHodges Read The Tip or you could simply do an 'isdate' check...  ie CREATE FUNCTION dbo.IsLeapYear(@yr int)RETURNS BIT AS BEGIN     DECLARE @retVal bit, @dte as datetime     SET @dte = '29 Feb ' + @Year     SET @retVal = isdate(@dte)     RETURN @retValENDGOSELECT dbo.IsLeapYear(1900) as IsLeapYear

 Thursday, June 26, 2008 - 6:21:33 AM - aprato Read The Tip SQL Server considers beginning of time as starting on Jan 1, 1753.   The next question is: Why? http://www.sqlskills.com/blogs/conor/2008/03/18/1753DatetimeAndYou.aspx  Unless you need to worry about calculating dates from the Middle Ages, it's inconsequential.

 Thursday, June 26, 2008 - 12:10:42 AM - tosc Read The Tip Hi aprato,  the official algorithm to determine leap years on the proleptic Gregorian calendar, which includes leap years before the official inception in 1582 is in Pseudocode: if year modulo 400 is 0 then leapelse if year modulo 100 is 0 then no_leapelse if year modulo 4 is 0 then leapelse no_leap there for you function doesn't work well -> select dbo.fn_IsLeapYear(1582) as '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``````

 Wednesday, June 25, 2008 - 2:06:08 PM - timothyrcullen Read The Tip Very interesting...thanks for the link!!!

 Wednesday, June 25, 2008 - 12:59:31 PM - aprato Read The Tip Hi TimContrary to common belief, leap years are not every 4 years.  There are exceptions to the "4 year" rule. http://www.dpbsmith.com/leapyearfaq.txtUsing the function the way I coded it, you don't have to worry about the rules since the engine does all figuring  for you.

 Wednesday, June 25, 2008 - 12:50:09 PM - timothyrcullen Read The Tip Good tip.  Lately I've been using the Modulo function for finding a leap year.  Since it's always every four years I use: case (datepart(year,getdate()) % 4) when 0 then 1 else 0 end

## Follow

Get Free SQL Tips

Pinterest

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Tutorials

Webcasts

Whitepapers

Tools

Tip Categories

Search By TipID

Top Ten

Authors

## Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

Join