Divide by number of days in the year.

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 advanceHere'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.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:19165261-4C92-45C9-97B5-00AC910B17FE@.microsoft.com...
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,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>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.
>
>|||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
"scuba79" wrote:

> 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|||:-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e%23m2LE3rFHA.1032@.TK2MSFTNGP12.phx.gbl...
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,getdat
e()))
Steve Kass
Drew University
Tom Moreau wrote:

>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.
>
>