ALTER'ing functions?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ALTER'ing functions??

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-15 : 16:07:25

I recently came across this and I'm not quite sure I understand the purpose of Altering a user defined function. I know you can do it and how to do it, but what's the reasoning?

I ask because I have a simple function that calculates the difference in years....

--Function Name: fn_Age
--Pre: @bday must be greater than @today
--Post: Returns the difference in years from @bday to @today.
ALTER function fn_Age(@bday datetime,@today datetime) returns int
as
begin
declare @years int
select @years = datediff(yy,@bday,@today)
if month(@bday)> month(@today)
set @years=@years-1
else
if month(@bday)= month(@today) and day(@bday)>day(@today)
set @years=@years-1
return @years
end

Not sure why they put "ALTER function fn_Age" in there when fn_Age is the function name *shrug* Can someone explain this?
--Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-15 : 16:16:15

It's just what you type when you want to modify the function's code. If you just want to create it, then you put CREATE FUNCTION.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 17:14:27

Here's a little shorter version of that function.


alter function fn_Age(@bday datetime,@today datetime) 
returns int 
as 
begin
declare @years int 
select @years = datediff(yy,@bday,@today) 
if dateadd(yy,@years,@bday) > @today
	set @years = @years-1
return @years 
end

CODO ERGO SUM

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-15 : 17:46:55

quote:
Originally posted by Michael Valentine Jones

Here's a little shorter version of that function.

alter function fn_Age(@bday datetime,@today datetime)
returns int
as
begin
declare @years int
select @years = datediff(yy,@bday,@today)
if dateadd(yy,@years,@bday) > @today
set @years = @years-1
return @years
end

CODO ERGO SUM

Nice, just take the years, add it to the bday and if that resulting date is greater than the current then you know the person's bday hasen't come yet so subtract one year. Lovely/clean. Thanks.
--Nick

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 18:00:42

Also, the version I posted fixes a subtle leap year problem. The version you posted would return a 0 for the following query, but mine would return a 1.

select dbo.fn_Age('2004-02-29','2005-02-28')

If you are interested, here is a function that returns age in years, months, and days.

Age Function F_AGE_YYYY_MM_DD
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

CODO ERGO SUM

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-15 : 20:28:27

quote:
Originally posted by Michael Valentine Jones

Also, the version I posted fixes a subtle leap year problem. The version you posted would return a 0 for the following query, but mine would return a 1.
select dbo.fn_Age('2004-02-29','2005-02-28')

If you are interested, here is a function that returns age in years, months, and days.

Age Function F_AGE_YYYY_MM_DD
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

CODO ERGO SUM

Coolness, I'll have a gander.
--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources