SQL SERVER – Various Leap Year Logics

Earlier I wrote one article on Leap Year and created one video about Leap Year. My point of view was to demonstrate how we can use SQL Server 2012 features to identify Leap year. How ever during the conversation I had some really good conversation. Here are updates for those who have missed reading the excellent comments on the blog.

Incorrect Logic

There are so many people still think Leap Year is the event which is consistently happening at every four year and the way to find it is divide the year with 4 and if the remainder is 0. That year is leap year. Well, it is not correct.

Comment by David Bridge

Check out this excerpt from wikipedia page

http://en.wikipedia.org/wiki/Leap_year

“most years that are evenly divisible by 4 are leap years…”

“…Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Years that are evenly divisible by 100 are not leap years, unless they are also evenly divisible by 400, in which case they are leap years. For example, 1600 and 2000 were leap years, but 1700, 1800 and 1900 were not. Similarly, 2100, 2200, 2300, 2500, 2600, 2700, 2900 and 3000 will not be leap years, but 2400 and 2800 will be.”

If you use logic of divide by 4 and remainder is 0 to find leap year, you will may end up with inaccurate result. The correct way to identify the year is to figure out the days of February and if the count is 29, the year is for sure leap year.

Valid Alternate Solutions

Comment by sainswor99insworth

IIF((@Year%4=0 AND @Year%100 != 0) OR @Year%400=0, 1,0)

Comment by Madhivanan

Madhivanan has written a blog post about an year ago where he listed multiple ways to find leap year.

Comment by Jayan

DECLARE @year INT
SET
@year = 2012
IF (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
PRINT ’1'
ELSE
print ’0'

Comment by David

DECLARE @Year INT = 2012
SELECT ISDATE('2/29/' + CAST(@Year AS CHAR(4)))

Comment by David Bridge

Incidentally – Another approach would be to take one day off March 1st and see if it is 29.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

2 thoughts on “SQL SERVER – Various Leap Year Logics

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s