How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year?  I know that I
can not just use 365 since it will not take into account leap years.

Thanks in advance

Here's how to get the number of days in the current year:

select
case
  when year (getdate()) / 100 % 4 = 0 then 365
  when year (getdate()) % 4 = 0 then 366
  else 365
end

However, in a UDF, you cannot have a non-deterministic function within it.
Thus, you cannot use getdate() directly.  However, you could feed a date to
the function.

How can I create a function that will divide a parameter passed into the
stored procedure by the number of days in the current year?  I know that I
can not just use 365 since it will not take into account leap years.

Thanks in advance

For fun, here's a compact way:

select 365+isdate(str(year(getdate()))+'0229')

and more fun:

select
  368-month(dateadd(yy,year(getdate())-1900,60))

and finally, one that's wrong, but rarely:

select
  datediff(d,getdate(),dateadd(yy,1,getdate()))

Steve Kass
Drew University

Tom Moreau wrote:

Show quoteHide quote

For fun, here's a compact way:

select 365+isdate(str(year(getdate()))+'0229')

and more fun:

select
  368-month(dateadd(yy,year(getdate())-1900,60))

and finally, one that's wrong, but rarely:

select
  datediff(d,getdate(),dateadd(yy,1,getdate()))

Steve Kass
Drew University

Tom Moreau wrote:

Show quoteHide quote

scuba
Here is a stright but bit complicated one. This is useful evenif calender
changes(yuck!) provided years starts from jan1 and ends with 31 dec(kidding)

SELECT DATEDIFF(DAY, CAST('01-01-' + cast(YEAR(GETDATE()) as varchar(4)) AS
DATETIME),CAST('12-31-' + cast(YEAR(GETDATE()) as varchar(4)) AS DATETIME))+ 1


Regards
R.D
Show quoteHide quote

Trouble with SP
SQLDataAdapter.FillSchema() hangs with select on view
Simplifying CHECK constraint
how to rename a temp table
SP plan different from batch