2

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

For Example:

  • 2016 : 366
  • 2014 : 365
Foolish
  • 3,952
  • 33
  • 46
ضرغام ملک
  • 39
  • 1
  • 1
  • 7

4 Answers4

4

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
Justin
  • 9,634
  • 6
  • 35
  • 47
  • Better would be to explicitly declare data type lengths (you can get bitten by the default length of 30 sometimes and 1 sometimes: http://stackoverflow.com/questions/1131499/what-is-the-effect-of-omitting-size-in-nvarchar-declaration). Also, you don't need NVARCHAR, VARCHAR should do the trick. Last, if you explicitly cast to DATEs it might be easier for the person who will have to possibly troubleshoot (and understand the code) later. Ex: DECLARE @Year INT = 2016; SELECT DATEDIFF(DAY, CAST(CAST(@Year AS CHAR(4)) AS DATE), CAST( CAST(@Year + 1 AS CHAR(4)) AS DATE)) AS Days_cnt; – HansLindgren Aug 03 '15 at 07:40
2

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

Dhaval
  • 2,341
  • 1
  • 13
  • 16
1

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.

Dan Sutton
  • 21
  • 3
1

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.

Jason
  • 41
  • 4