Wednesday, January 13, 2016

Is this a leap year? Simple test in SQL


SELECT Isdate('02/29/' 
              + Cast(Year(@YOURDATE) AS CHAR(4))) AS isTHISISALEAPYEAR 

Very quick, very simple test.  If February 29th is a date, then it is a leap year.  Let SQL server do the work for you on this one.

Here is an expanded example to just get the information for any set of years.

DECLARE @StartYear INT = '1990' 
DECLARE @EndYear INT = '2020'; 

WITH cte_makedates 
     AS (SELECT @StartYear YEAR_NB --Start Date  
         UNION ALL 
         SELECT year_nb + 1 
         FROM   cte_makedates 
         WHERE  year_nb + 1 < = @EndYear --End date  
       Isdate('02/29/' + Cast(year_nb AS CHAR(4))) AS isTHISISALEAPYEAR 
FROM   cte_makedates