sql calculate total number of day in a given year

To get accurate number of days in a given year, we have to check if the given year is a leap year or not.  To see if it is a leap year, the year part of date (for example 2016 of 02/24/2016) should be divisible by 4 and there should be no remainder.

So let's see how we do this is SQL (2008 and above version)

First we need to declare a variable (BIT Type) and default it to 0.

Declare @IsLeapYear BIT = 0;

Then declare date variable for which you need to calculate. In my case I am using getdate() to set to current year and we know it is leap year

Declare @MyYear Date = Getdate();

Now we add logic to check if it leap year or not

    IF (YEAR( @MyYear ) % 4 = 0 AND YEAR( @MyYear ) % 100 != 0) OR
        YEAR( @MyYear ) % 400 = 0
        SET @IsLeapYear = 1

This way we know if it is leapyear, it will set @IsLeapYear to 1 and if not it will be 0

 Select 365 + @IsLeapYear

This select statement will tell me number of days in a given year.

So you whole SQL statement should be something like this.

 DECLARE @IsLeapYear BIT = 0       , @MyYear DATE = getdate()

    IF (YEAR( @MyYear ) % 4 = 0 AND YEAR( @MyYear ) % 100 != 0) OR
        YEAR( @MyYear ) % 400 = 0
        SET @IsLeapYear = 1

    Select 365 + @IsLeapYear

2012 and above version



DECLARE @year AS INT
SET @year=2013
Select DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [NumberofDaysInYear]