SQL SERVER – Various Leap Year Logics
- by pinaldave
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)
Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
