CODEWITHSUNDEEP
sql-server-2008
ضرغام ملک
ضرغام ملک

Reputation: 49

how to calculate number of days in year in sql server 2008

I want to calculate the number of days in year in SQL server 2008 management studio.

For Example:

Upvotes: 3

Views: 28856

Answers (4)

Jason
Jason

Reputation: 51

For SQL 2012 and later:

DECLARE @FromDateYear INT = YEAR(GETDATE());
SELECT DATEDIFF(DAY, DATEFROMPARTS(YEAR(@FromDateYear),1,1), DATEFROMPARTS(YEAR(@FromDateYear) + 1,1,1))

You can simply substitute the YEAR(GETDATE()) with your year value.

All this is doing is calculating the number of days from 1 Jan of the current year and 1 Jan of the next year. This will include Feb 29 if it exists in the current year.

Upvotes: 2

Dan Sutton
Dan Sutton

Reputation: 21

This also works: 

begin  
  declare @y int  
  set @y=2014  

  select datepart(dayofyear,dateadd(day,-1,'1/1/'+convert(char(4),@y+1)))  
end

I'm getting day 1 of the next year, subtracting one day to get the last day of the current year, then getting the day-of-year from that.

Upvotes: 1

Justin
Justin

Reputation: 9724

You can try:

  declare @y int;
  set @y = 2014;

  SELECT DATEDIFF(day,  cast(@y as char(4)),  cast(@y+1 as char(4))) Days

Result:

Days
365

2Version by comment:

 declare @y int;
 set @y = 2014;

 SELECT DATEDIFF(day,  cast(cast(@y as char(4)) as date),  cast(cast(@y+1 as char(4)) as date)) DaysCnt

Upvotes: 5

Dhaval
Dhaval

Reputation: 2379

Try this...

create function fn(@year varchar(20))
returns int
as
begin   
declare @a int
select @a =DATEPART(dy,@year +'1231')
return @a
end

select dbo.fn('2014')--365
select dbo.fn('2016')--366

DEMO Fiddle

Upvotes: 2

Related Questions