Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER | Find Number of Days in a Year

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)

Method 1 | Using Simple Maths & IIF/CASE Statements


/*

Find no of days in a Year - Leap Year / Non Leap Year
Every year has 365 days
If the year is leap then we have to add 1 ,
so logic will be 365 + add 1 if the year is leap

*/

DECLARE @YEAR AS INT = 2012
SELECT NofDays = 365 + ISNULL(CASE WHEN @YEAR%400=0 THEN 1
			 WHEN @YEAR%4=0 THEN 1
			 WHEN @YEAR%100=0 THEN 0
			 END,0)

GO

DECLARE @YEAR AS INT = 2012
SELECT NofDays = 365 +
 IIF( @YEAR%400 =0 , 1 , IIF(@YEAR%4 =0, 1, IIF(@YEAR%100 = 0,0,0)))
GO

--

Method 2 | Using Recursive CTE


--

/*

Method 2 | Using Recursive CTE
Note | We have to add OPTION (MAXRECURSION 366)
If the above option is not sepecified below error will come
"Msg 530, Level 16, State 1, Line 11
 The statement terminated. The maximum recursion 100 has been exhausted
 before statement completion."

*/

DECLARE @YEAR AS INT = 2015
;WITH CTE AS
(
	SELECT CAST(CONCAT('01/01/',@YEAR) AS DATETIME) y1
	UNION ALL
	SELECT DATEADD(d,1,y1) y1
        FROM CTE
        WHERE DATEADD(d,1,y1) <= CAST(CONCAT('12/31/',@YEAR) AS DATETIME)
)
SELECT COUNT(*) NoOfDays FROM CTE
OPTION (MAXRECURSION 366)

--

Method 3 | Using DateDiff


--

/*

Method 3 | Using DateDiff and Cast/Convert

*/

DECLARE @YEAR AS INT = 2012
SELECT
    DATEDIFF(d,CAST(CONCAT('01/01/',@YEAR) AS DATETIME)
    ,CAST(CONCAT('12/31/',@YEAR) AS DATETIME) + 1)

--

Method 4 | Using New DATEFROMPARTS & DATEPART


--

/*

Method 4 | Using New DATEFROMPARTS & DATEPART

*/

DECLARE @YEAR AS INT = 2012
SELECT NofDays = DATEPART(dy,DATEFROMPARTS(@YEAR,12,31))

--