Feeds:
Posts

## SQL SERVER – Multiple ways to calculate the number of days in a Year

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
,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
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.

### One Response

1. on April 13, 2015 at 3:13 pm | Reply Pawan Kumar

Hi,

I am not sure about the best methods but I like the below one most–

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

Other methods are also available @

http://msbiskills.com/2015/04/13/sql-server-find-number-of-days-in-a-year/

Regards,

Pawan Kumar Khowal

http://MSBISkills.com