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

 Hi Tim

Contrary to common belief, leap years are not every 4 years.  There are exceptions to the "4 year" rule.

 http://www.dpbsmith.com/leapyearfaq.txt

Using the function the way I coded it, you don't have to worry about the rules since the engine does all figuring  for you.

Very interesting...thanks for the link!!!

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

 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.

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 @retVal
END
GO

SELECT dbo.IsLeapYear(1900) as IsLeapYear 

This is the code I use to calculate a leap year, its much more efficient when doing the calculation over several hundred thousand rows...

CREATE FUNCTION dbo.fn_IsLeapYear (@year INT)

DECLARE @fourYearRule INT

DECLARE @oneHundredYearRule INT

DECLARE @fourHundredYearRule INT

SET @oneHundredYearRule = @year % 100

IF (@fourYearRule = 0 AND (@oneHundredYearRule <> 0 AND @fourHundredYearRule <> 0))

IF (@fourYearRule = 0 AND @oneHundredYearRule = 0 AND @fourHundredYearRule = 0)

RETURN 0

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 BIT
AS BEGIN
    RETURN CASE DAY((
                     SELECT DATEADD (day, - 1, CAST( @year AS CHAR (4)) + '-03-01')
                    ))
             WHEN 29 THEN 1
             ELSE 0
           END

   END

There are various ways of "skinning a cat" as they say. The take away..... let the engine do the work for you! 

 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.

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 :-) ].

Create Function dbo.Fn_IsLeapYear(@Year Bigint)
Returns Bit
AS
Begin
Declare @R4 Int,
        @R100 Int,
        @R400 Int,
        @IsLeap Bit
Set @R4 = @Year % 4
Set @R100= @Year % 100
Set @R400= @Year % 400
Set @IsLeap = 0
if (@R4 = 0 )
  Begin
   if (@R400 = 0)
       Set @ISLeap=1
   else if (@R100 <>0)
          Set @ISLeap=1
  End
Return @IsLeap
End
Select .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

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:

1. creating each as a UDF
2. creating a table with all the years from 17753 to 9999
3. 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:

1. tosc (0 functions)                time: 4531 milliseconds
2. rajan (0 functions)               time: 4739
3. vedmondson (0 functions)    time: 4864
4. khodges (2 functions)          time: 5905
5. johnrogerson (3 functions)    time: 92584
6. 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:

1. isdate('29 Feb ' + TheCYear)                          time: 2195
2. 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.

 steve, excellent analysis!

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.

 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

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:

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 is

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.

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

Not sure why anyone actually needs a function for this...

DECLARE @Year INT
    SET @Year = 2007
 SELECT ISDATE(STR(@Year)+'0229')

But CAST or CONVERT are quicker than STR  ;-)

;with dateStrings as
(select str(N) as xYear
 from tally
 where N between 1000 and 9000)
select sum(isdate(xYear+'0229')) from datestrings

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.

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.

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...

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 

Sure... just doesn't have a thing to do with the subject at hand.

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 is
END