SQL Server Portal

How to calculate the number of days in a year ? Was a question I came across and began with my usual research for the solution. Given below are few methods.

Method 1 :
In this method, I calculated the no of days in a year using the days difference between 1 Jan of the current year and the next year.
Given below are the scripts.

--This method will work on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEDIFF(DAY,DATEADD(YEAR,@year-1900,0)
,DATEADD(YEAR,@year-1900+1,0)) AS [TOTAL NO OF DAYS]
GO
--This technique is same as above but using new datefromparts
--function introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.

DECLARE @Year INT =2012
SELECT DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1)
, DATEFROMPARTS(@year+1,1,1)) AS [TOTAL NO OF DAYS]
GO

Method 2 :
In this method, I checked whether February had 28 days or 29. Obviously, if 29 days it means total no of days in a year is 366 else 365.
Given below are the scripts.

--This method will work on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year=2012
SELECT (Case When Day(DATEADD(YEAR,@year-1900,0)+59)=29
Then 366 else 365 end) AS [TOTAL NO OF DAYS]
GO
--This technique is same as above but using new datefromparts and
--EOMONTH functions introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
SELECT (Case When  DAY(EOMONTH(DATEFROMPARTS(@Year,2,1)))=29
Then 366 else 365 end) AS [TOTAL NO OF DAYS]

Method 3 :
In this method, I checked the day of last date of the year e.g 31st Dec 2012.

--This method will work on SQL server 2005 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEPART(dy,(DATEADD(YEAR,@year-1899,0)-1))
AS [TOTAL NO OF DAYS]

--This technique is same as above but using new datefromparts
--functions introduced in SQL Server 2012.
--This technique will work in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year=2012
Select DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [TOTAL NO OF DAYS]

Conclusion :
We have multiple ways to calculate the number of days but I recommend to use the Method 3, as it has shortest way to calculate the number of days in a year.

Let me know if you have a better way to achieve it.