How to find out if this is a leap year with MSSQL

According to wikipedia:

  • The average year length of 365.2425 days.
  • We add a 29th day to February every 4 years, almost.

Leap year algorithm

As per Wikipidia 

 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

Meaning: 1896 was a leap year, and the next leap year was 1904. While the year 2000 was leap year, 2100 will not be a leap year. Leap years affect when calculating days, for aging.

Microsoft with SQL Server has already done all the work, so how do you know when this is a leap year or not. Now for some obvious observations:

  1. A leap year has 366 days.
  2. February has 29 days in a leap year.
  3. A non leap year has 365 days.
  4. February has 28 days in a non leap year.

Leap year calculation: alternative 1

Count the number of days for that year. If there are 365 days then it's not a leap year, if there are 366 days then it's a leap year.

Not a leap year

  1. SELECT CURRENT_TIMESTAMP AS 'today',
  2.        CAST((STR( YEAR( CURRENT_TIMESTAMP ) ) + '/01/01') AS SMALLDATETIME) AS 'Current year',
  3.        CAST((STR( YEAR( CURRENT_TIMESTAMP ) +1 ) + '/01/01') AS SMALLDATETIME) AS 'Next year',
  4.        DATEDIFF(DAY,CAST((STR( YEAR( CURRENT_TIMESTAMP ) ) + '/01/01') AS SMALLDATETIME),CAST((STR( YEAR( CURRENT_TIMESTAMP ) +1 ) + '/01/01') AS SMALLDATETIME)) AS 'Non Leap year: Num of days';
  5. go
today                   Current year            Next year               Non Leap year: Num of days
----------------------- ----------------------- ----------------------- --------------------------
2007-07-29 18:02:15.770 2007-01-01 00:00:00     2008-01-01 00:00:00     365

(1 row(s) affected)

Leap year

The year 2000 was a leap year.

  1. SELECT DATEDIFF(DAY,'2000/01/01','2001/01/01') AS 'Leap year: Num of days';
  2. go
Leap year: Num of days
----------------------
366

(1 row(s) affected)

Leap year calculation: alternative 2

Count the number of days in February. If there are 28 days then it's not a leap year, if there are 29 days then it's a leap year.

Not a leap year

  1. SELECT DATEDIFF(DAY,CAST((STR( YEAR( CURRENT_TIMESTAMP ) ) + '/02/01') AS SMALLDATETIME),CAST((STR( YEAR( CURRENT_TIMESTAMP ) ) + '/03/01') AS SMALLDATETIME)) AS 'Non leap year: Num of days in February';
  2. go
Non leap year: Num of days in February
--------------------------------------
28

(1 row(s) affected)

Leap year

  1. SELECT DATEDIFF(DAY,CAST(('2000/02/01') AS SMALLDATETIME),CAST(('2000/03/01') AS SMALLDATETIME)) AS 'Leap year: Num of days in February 2000';
  2. go
Leap year: Num of days in February 2000
---------------------------------------
29

(1 row(s) affected)