Calculating leap years and date differences that accurately reflect leap years in SQL is often discussed online but I have noticed that most presented solutions are just way too complicated. SQL Leap Year calculations can be so simple. Here I’ll show you how to do it.
There are two main calculations regarding leap years in SQL that come up over and over:
- 1. What is the accurate number of years between two dates, considering leap years?
- 2. How can I check whether a given year is a leap year?
Year difference between two dates
Using SQL’s DATEDIFF function to calculate the number of years between two dates is spotty. For one, it rounds, which is not too good if the actual number you are interested is the age of a person, for instance. Let’s look at 2 random dates: A person born on 5/6/1934 would be almost 45 years old on 4/5/1979 but not quite yet. So, instead of using:
select DATEDIFF(YEAR, '5/6/1934', '4/5/1979')which gives you the answer 45, you could use:
select DATEDIFF(DAY,'5/6/1934', '4/5/1979')/365.242What we do here is simply getting the number of days rather than the number of years and then dividing the result by the number of days in a year. Now a year does not last exactly 365 days but a tiny bit longer. How much longer exactly depends on how you look at it but 0.242 days is a close enough value. This is why we have leap years every four years in the first place. And the reason why there are special exceptions to the 4 year rule can be seen in this value: it is close to a quarter day, but not exactly.
The code above will give you a decimal of the actual age in years: 44.915
Of course we do not use age that way, someone who is 44.915 years old is considered to be of age 44. The easiest way is to round down and rounding down, regardless of how close the decimal digits are, is the FLOOR function:
select FLOOR(DATEDIFF(DAY,'5/6/1934', '4/5/1979')/365.242)This will give you the most accurate age. Unlike when using DATEDIFF(YEAR), this consideres whether or not a person already had their birthday or if it is still coming up in that year. It also takes leap years into account and leap years in SQL is what we are after here.
Checking if a year is a leap year in SQL
The coding madness I have seen here is just mind-boggling. Some people wrote functions, dozens of lines of code and complicated like nothing you have ever seen before. Some create new date variables for February 28th of the year in question, add a day and then check whether the result is in February of in March. Others change the year integer into a varchar and then dissect it over several steps, based on the digits. Even others came up with temp tables of all possible years to compare the requested year to. All these work but this is not necessary!
Let’s quickly recap what the rules for leap years are:
- If a year is divisible by 4 then it is a leap year
- The exception is whole centuries, such as 1800 or 1900, which are divisible by 4 but are not leap years
- We can say that a year divisible by 100 is not a leap year
- An exception to this rule is that if a year is divisible by 100 but also by 400, it is again a leap year. For example, 1600 or 2000.
So, how do we calculate Leap Years in SQL in a simple and quick way?
declare @Y as int = 1934
select case when @Y % 400 = 0 then 1
when @Y % 100 = 0 then 0
when @Y % 4 = 0 then 1
else 0 end as IsLeapYearA simple three line case statement using modulus, which is denoted by the percentage symbol in SQL. If you remember, modulus is simply the “remainder” of a division. We don’t care how often we can divide something, we just care about the remainder. If a year, say 1934, is divisible by 4 and we divide it by 4 then the remainder is 0. Another word of saying this is 1934 mod 4 = 0. If it is not divisible by 4 (and 1934 is not) then the remainder will not be 0.
Now all we need to do is checking this for 4 years, 100 years and 400 years and put the checks in the right order of precedence, as we did above. No need for complicated checks and cycle-through routines. This is how you deal with leap years in SQL.
