determine days of the year

Hi there, is that any function in ms sql server 2000 where if i pass a date or a year then it could gives me the total days of year from the parameter?
in mySQL got select DAYOFYEAR(date);
can some one guide me on this please...i need to use it for a leap year function for my SP!There's no built-in function that will return the number of days in a year for a given date.However you can create a function something like

CREATE FUNCTION [dbo].[GetDaysInYear] ( @.pDate DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @.IsLeapYear BIT

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

RETURN 365 + @.IsLeapYear

END
GO

Then you can call your function directly in your SP like

GetDaysInYear (Your date goes here)|||CREATE FUNCTION GetDaysInYear
(
@.pDate DATETIME
)
RETURNS INT
AS
BEGIN
Declare @.days int
Declare @.firstDay varchar(15)
set @.firstDay = '1/1/' + Cast(Year(@.pDate) as varchar)
Select @.days = DATEDIFF(dd, Convert(Varchar, @.firstDay, 101), @.pDate)
RETURN @.days
END
GO

This function behaves similar to MySQL DAYOFYEAR(date) function
(only diff. is I think with 1-Jan)|||mihir, you were close but the calculation is off by 1 for all dates

:)|||Hi Thanx nick.ncs

i think i can just use your :

IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0

it wats i need after all!
thank you so much nick.ncs :eek:|||another way is to check if the 60th day of the year is in feb. see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83637|||declare @.dt datetime
set @.dt='2007-06-18'
select datediff(dd,
dateadd(yy,datediff(yy,0,@.dt),0)
,dateadd(yy,datediff(yy,0,@.dt)+1,0)
) DaysInYear|||pdreyer, that is gorgeous

wherever did you learn that?

:)|||select datepart(dy, getdate())|||mcrowley, welcome to the thread

please note your query produces day in year

the problem was days in year

i.e. either 365 or 366

:)|||but your idea was a good one :)

select datepart(dy, dateadd(dd,-1,dateadd(yy,datediff(yy,0,@.dt)+1,0)))|||Doh !|||don't beat yourself up about it, you still had a great idea, but it would only have worked for Dec 31st, so that's what i changed it slightly to do

:)