Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server Function to Determine a Leap Year

MSSQLTips author Armando Prato By:   |   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 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?'
 
 

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

Next Steps



Last Update: 6/25/2008


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
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


Wednesday, June 25, 2008 - 12:59:31 PM - aprato Read The Tip

 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 - 2:06:08 PM - timothyrcullen Read The Tip

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


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

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.

 


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

SELECT dbo.IsLeapYear(1900) as IsLeapYear 

 


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 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 - 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 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 - 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: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 - 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 - 10:00:43 PM - Rajan Read The Tip

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


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:

  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.

 


Tuesday, July 15, 2008 - 6:16:38 AM - aprato Read The Tip

 steve, excellent analysis!


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.


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


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

 

 


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

 


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


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


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.


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.


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


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 


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.


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


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


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)

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.