28 | February | 2018 | Improving my SQL BI Skills

Tags

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

Ref – https://www.mathsisfun.com/leap-years.html

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)

--

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 Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

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

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

Share this