SQL Server Function to Determine a Leap Year

By:   |   Comments (28)   |   Related: > 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 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.

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

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

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, October 24, 2013 - 2:55:25 AM - saikumar Back To Top (27253)

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 Back To Top (27241)

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 Back To Top (2599)

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


Tuesday, December 16, 2008 - 12:00:07 AM - Jeff Moden Back To Top (2409)

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

Monday, December 15, 2008 - 11:25:40 PM - alec.nolan Back To Top (2408)

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 Back To Top (2388)

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 Back To Top (2386)

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 8, 2008 - 6:57:43 PM - Jeff Moden Back To Top (2367)

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 8, 2008 - 4:59:36 PM - Bob Hovious Back To Top (2366)

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 Back To Top (2087)

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 Back To Top (1467)

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 Back To Top (1447)

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 is

END

 

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 Back To Top (1438)

 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 Back To Top (1437)

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 Back To Top (1433)

 steve, excellent analysis!

Tuesday, July 15, 2008 - 12:36:55 AM - steve Back To Top (1430)

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.

 

Monday, July 14, 2008 - 10:00:43 PM - Rajan Back To Top (1427)

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

Monday, July 14, 2008 - 8:11:53 AM - ScottPletcher Back To Top (1420)

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 Back To Top (1419)

 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 Back To Top (1418)

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 Back To Top (1417)

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

Monday, July 14, 2008 - 4:15:26 AM - VEdmondson Back To Top (1416)

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 INT

SET @fourYearRule = @year % 4

SET @oneHundredYearRule = @year % 100

SET @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 0

END

GO

Monday, July 14, 2008 - 1:33:42 AM - KHodges Back To Top (1415)

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 

 

Thursday, June 26, 2008 - 6:21:33 AM - aprato Back To Top (1262)

 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 Back To Top (1255)

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

Wednesday, June 25, 2008 - 2:06:08 PM - timothyrcullen Back To Top (1252)

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

Wednesday, June 25, 2008 - 12:59:31 PM - aprato Back To Top (1251)

 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.


Wednesday, June 25, 2008 - 12:50:09 PM - timothyrcullen Back To Top (1250)

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














get free sql tips
agree to terms