## SQL SERVER | Find whether the year is a Leap year or not? | SQL Interview Question

There are two types of Years – Leap year and Non Leap year. Lets first understand what are they-

Leap Year

• A normal year has 365 days.
• A Leap Year has 366 days (the extra day is the 29th of February).

## How to know if a year will be a Leap Year:

 Leap Years are any year that can be evenly divided by 4 (such as 2012, 2016, etc) except if it can can be evenly divided by 100, then it isn’t (such as 2100, 2200, etc) except if it can be evenly divided by 400, then it is (such as 2000, 2400)

–Find Whether a Year is a Leap Year or NOT?

–SOLUTION 1

 ```-- --Example1 DECLARE @Yr INT = 2018 SELECT @Yr [Year] , IIF ((@Yr % 4 = 0 AND @Yr % 100 != 0) OR @Yr % 400 = 0, 'Leap Year','Not a Leap Year') [IsLeapYear] GO OUTPUT Year IsLeapYear ----------- --------------- 2018 Not a Leap Year (1 row affected) -- ```
 ```-- --Example2 DECLARE @Yr INT = 2012 SELECT @Yr [Year] , IIF ((@Yr % 4 = 0 AND @Yr % 100 != 0) OR @Yr % 400 = 0, 'Leap Year','Not a Leap Year') [IsLeapYear] GO Year IsLeapYear ----------- --------------- 2012 Leap Year (1 row affected) -- ```

–SOLUTION 2

 ```-- --Example1 DECLARE @Yr INT = 2018 SELECT @Yr [Year] , IIF ( DAY(EOMONTH(DATEFROMPARTS(@yr,2,1))) = 29 , 'Leap Year','Not a Leap Year') [IsLeapYear] GO Year IsLeapYear ----------- --------------- 2018 Not a Leap Year (1 row affected) -- ```
 ```-- --Example2 DECLARE @Yr INT = 2012 SELECT @Yr [Year] , IIF ( DAY(EOMONTH(DATEFROMPARTS(@yr,2,1))) = 29 , 'Leap Year','Not a Leap Year') [IsLeapYear] GO Year IsLeapYear ----------- --------------- 2012 Leap Year (1 row affected) -- ```

–Find Whether a Date is a Leap Year or NOT?

–SOLUTION 1

 ``` -- --Example1 DECLARE @Yr INT = YEAR('2012/01/01') SELECT @Yr [Year] , IIF ((@Yr % 4 = 0 AND @Yr % 100 != 0) OR @Yr % 400 = 0, 'Leap Year','Not a Leap Year') [IsLeapYear] GO OUTPUT Year IsLeapYear ----------- --------------- 2012 Leap Year (1 row affected) -- ```
 ```-- --Example2 DECLARE @Yr INT = YEAR('2018/01/01') SELECT @Yr [Year] , IIF ((@Yr % 4 = 0 AND @Yr % 100 != 0) OR @Yr % 400 = 0, 'Leap Year','Not a Leap Year') [IsLeapYear] GO OUTPUT Year IsLeapYear ----------- --------------- 2018 Not a Leap Year (1 row affected) -- ```

–SOLUTION 2

 ```-- --Example1 DECLARE @Yr INT = YEAR('2018/01/01') SELECT @Yr [Year] , IIF ( DAY(EOMONTH(DATEFROMPARTS(@yr,2,1))) = 29 , 'Leap Year','Not a Leap Year') [IsLeapYear] GO OUTPUT Year IsLeapYear ----------- --------------- 2018 Not a Leap Year (1 row affected) -- ```
 ```-- --Example2 DECLARE @Yr INT = YEAR('2012/01/01') SELECT @Yr [Year], IIF ( DAY(EOMONTH(DATEFROMPARTS(@yr,2,1))) = 29 , 'Leap Year','Not a Leap Year') [IsLeapYear] GO --OUTPUT Year IsLeapYear ----------- --------------- 2012 Leap Year (1 row affected) -- ```

SOLUTION 3

 ```-- --Example1 DECLARE @Yr INT = YEAR('2012/01/01') SELECT @Yr [Year] , IIF ( TRY_CAST ( CONCAT( @yr , '/2/29') AS DATE) IS NOT NULL , 'Leap Year','Not a Leap Year') [IsLeapYear] GO Year IsLeapYear ----------- --------------- 2012 Leap Year (1 row affected) -- ```
 ```-- --Example2 DECLARE @Yr INT = YEAR('2018/01/01') SELECT @Yr [Year], IIF ( TRY_CAST ( CONCAT( @yr , '/2/29') AS DATE) IS NOT NULL , 'Leap Year','Not a Leap Year') [IsLeapYear] GO Year IsLeapYear ----------- --------------- 2018 Not a Leap Year (1 row affected) -- ```

Related Puzzle

Note – If you do not have SQL 2012+ then you need to use CASE statement instead of IIF.

Please add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn. Thanks in advance.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/