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
 General SQL Server Forums
 New to SQL Server Programming
 function question

Author  Topic 

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-29 : 18:35:32
How can i calculate the age of a person in years. Taking into account the birth date may have not occurred yet.

How can write a user defined function for it? I just started with functions, so any insight would be very thankfull.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 19:00:26
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-29 : 19:00:30
You mean a function for : Year(getdate()) - @BirthYear ?

************************
Life is short. Enjoy it.
************************
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-29 : 19:06:04
Damn!! looks like every topic has been discussed over here. Perhaps we should advice users to first search these forums for their question. It will save time for everyone..and also less posts = less DB size for the forums..

************************
Life is short. Enjoy it.
************************
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 19:06:28
Some things to watch out for when using DATEDIFF function.
http://www.sqlteam.com/item.asp?ItemID=26922


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-29 : 19:48:36
Select name, dateOfBirth as dob, day(dateOfBirth) as d, month(dateOfBirth) as m, year(dateOfBirth) as y, Year(getdate()) – year(dateOfBirth) as age
From employee
Group by dateOfBirth



how can write this in user defined function and it has no end date, i'm new to functions. Any help would be appreciated.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 20:16:39
can you explain these statement ? What do you want ?

" How can i calculate the age of a person in years. Taking into account the birth date may have not occurred yet"

"how can write this in user defined function and it has no end date"


KH

Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-30 : 18:07:29
User will input the current date---I'm having trouble putting it into the functions.

CREATE function dbo.dateofbirth ( )
returns
as
BEGIN

....

end
return @result
END

--Function call script Tests
Select dbo.dateofbirth(‘’)----user input the date and get the result
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-30 : 21:18:11
[code]
create function dbo.fn_age(@birthdate datetime, @today datetime)
returns int
as
begin
return (select datediff(year, @birthdate, @today))
end
go

select dbo.fn_age('2005-01-01', getdate())
[/code]


KH

Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-31 : 18:36:12
how can i do it without using the datediff function and have to calculate months for age also


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-31 : 22:30:31
What's wrong with using datediff() ?


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-01 : 03:35:16
You can use a WHILE loop...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 03:52:19
quote:
Originally posted by Peso

You can use a WHILE loop...


Peter Larsson
Helsingborg, Sweden


and increment counter by 1 second


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-01 : 07:14:25
Exactly!
You will get a more precise YEAR and MONTY answer


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 08:53:30
April's Fool Joke ?


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-01 : 09:29:35
Absolutely!

TADIN, here is a inefficient way to calculate the difference in months WITHOUT using datediff.
I can't think of why anyone would like to do this though...
CREATE FUNCTION dbo.fnGetMonths
(
@anyDay DATETIME,
@theDay DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Temp DATETIME,
@Diff INT

IF @anyDay > @theDay
SELECT @Temp = @anyDay,
@anyDay = @theDay,
@theDay =@Temp

SELECT @Diff = -1,
@anyDay = CAST(CONVERT(VARCHAR, @anyDay, 112) AS DATETIME),
@theDay = CAST(CONVERT(VARCHAR, @theDay, 112) AS DATETIME)

WHILE @anyDay <= @theDay
SELECT @Diff = @Diff + 1,
@anyDay = DATEADD(MONTH, 1, @anyDay)

RETURN @Diff
END


SELECT dbo.fnGetMonths('19760608', '20070401') AS [Difference in months]


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 09:37:27
This is the first time i have ever seen Peter posted an inefficient solution


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-01 : 10:38:26
Hey! Don't blame me
Somehow I thought I hade to meet TADIN on his own level of SQL Server skills
The code above will not work anyway if starting day is the 31st. Or 30th or 29th if jumping over february.

TADIN wouldn't accept the first two suggestions I made...
It seems to me he wants to reinvent use of fire, the wheel and brain surgery all over again.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-01 : 22:03:56
I'm still working on my skills, i'm still confused what i'm passing in?I will to figure out, something. Any help would be thankful.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 22:11:51
quote:
Originally posted by tadin

I'm still working on my skills, i'm still confused what i'm passing in?I will to figure out, something. Any help would be thankful.


Maybe you can post your table DDL (table structure), some sample data and the result that you want.


KH

Go to Top of Page

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-04-01 : 22:38:41
lets say i want to pass a date...forinstance today's date...2007-04-01 and i would like to call a function by passing the argument in the select statement.

select dbo.fnGetMonths('2007-04-01') -- and would like to get an integer 4



CREATE FUNCTION dbo.fnGetMonths
(
@
)
return int
as
Begin
.....
End
Go to Top of Page
    Next Page

- Advertisement -