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=74462Peter LarssonHelsingborg, Sweden |
|
|
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.************************ |
|
|
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.************************ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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 ageFrom employeeGroup 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. |
|
|
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 |
|
|
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 asBEGIN....endreturn @resultEND--Function call script TestsSelect dbo.dateofbirth(‘’)----user input the date and get the result |
|
|
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 intasbegin return (select datediff(year, @birthdate, @today))endgoselect dbo.fn_age('2005-01-01', getdate())[/code] KH |
|
|
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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-31 : 22:30:31
|
What's wrong with using datediff() ? KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-01 : 03:35:16
|
You can use a WHILE loop...Peter LarssonHelsingborg, Sweden |
|
|
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 LarssonHelsingborg, Sweden
and increment counter by 1 second KH |
|
|
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 LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-01 : 08:53:30
|
April's Fool Joke ? KH |
|
|
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 INTASBEGIN 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 @DiffEND SELECT dbo.fnGetMonths('19760608', '20070401') AS [Difference in months]Peter LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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. |
|
|
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 |
|
|
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 intas Begin.....End |
|
|
Next Page
|