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]

Inserting Data in temp table from Another table

Sometime when we update certain table, its always a good idea to save the entire data into another temporary table which will hold unless we delete that temp table.

Let's say we are working on a Employees table and we need to manually update certain rows or columns for this table.

To do so, let's back them up somewhere for our reference (incase we mess up whole table..I have done few times in beginning!!)


Select * INTO tmpEmployees
FROM Employees

This statement will create a table "tmpEmployees" which will have same structure as  Employees table.


Even we when we close current session, this table will remain there in our database.


However, if you do not want to retain your temp table after you are done with you update, you can create #tmp table


Select * INTO #tmpEmployees
FROM EMPLOYEES


If table already exist, than we have to use this


INSERT INTO #tmpEmployees 
Select * FROM Employees


:Kumar