MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1527

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!