| Author |
Topic |
|
tadin
Yak Posting Veteran
63 Posts |
 Posted - 2007-03-27 : 20:04:06
|
| I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?This is how i did it, i couldn't finish it, Any feedback would be very thankful. select name, datebirth, year(dateofbirth)from employee |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-03-27 : 22:14:26
|
| Check out the DateDiff function: DateDiff (Year, Date1, Date2) ************************Life is short. Enjoy it.************************ |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-03-27 : 22:20:21
|
| There is an article on the home page that addresses this very issue: http://www.sqlteam.com/item.asp?ItemID=26922===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
tadin
Yak Posting Veteran
63 Posts |
Posted - 2007-03-28 : 00:23:55
|
| How can i do it without using the datediff function? I just want to use month(), year() and day()function so how can i do it?select dateofbirth, day(dateofbirth)as d, month(dateofbirth) as m, year(dateofbirth)as y, 2007-year(dateofbirth) as agefrom employeegroup by dateofbirth;employee tablecolumnsdateofbirth d m y age1937-01-20 20 1 1937 ?i'm trying to get the age. I want to get the result 70, but instead of using 2007 i would like to solve it, because every year would be changing?Any feedback would be appreciated.Thankyou in advance. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-03-28 : 00:33:31
|
is datediff not allowed in your homework problem? 
www.elsasoft.org |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-28 : 00:59:14
|
are you allowed to use getdate() ? If not you can use CURRENT_TIMESTAMPselect dateofbirth, day(dateofbirth)as d, month(dateofbirth) as m, year(dateofbirth)as y, year(getdate()) - year(dateofbirth) as agefrom employee
KH |
|
|
|
tadin
Yak Posting Veteran
63 Posts |
Posted - 2007-03-28 : 01:07:48
|
| Thanks so much. i was looking at the below website. You know, its been 4 weeks i started out sql. One week i joined this forum. I didn't have the idea to put together get() function with the year()...Thanks a lot. I just pasted the below link for other people incase they run into the same issue. What you posted was awesome.You guys are great..Thankyou.http://www.sql-server-helper.com/faq/dates-p01.aspx |
|
|
|
dryade
Starting Member
2 Posts |
Posted - 2007-05-02 : 08:47:34
|
| I have got about the same problem. Difference is that I need to put a table on the screen that has a column showing the calculated age next to the columns with name and date of birth stored in the database table. I have got a UDF to calculate the ages, but I have trouble using the function in my SELECT query. I have tried several other ways to do this as well, but keep encountering the same problem: For the function I need the values from the SELECT query (the birth date) and for the SELECT query, I want to use the calculated ages as well.I hope you understand my problem and can help me with a solution to it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
dryade
Starting Member
2 Posts |
Posted - 2007-05-02 : 10:01:20
|
| I have had a couple of years experience with SQL, but I am converting an access database to sql server. Also I am trying out things like stored procedures and transactions for the first time. Because I am supposed to learn to work with those stored procedures, I have to do as much with them as possible (and wise ofcourse). Since I have investigatged about 5 different solutions to this problem by now, which all stranded more or less halfway there, posting all that code here does not seem wise to me. Maybe if someone has a suggestion which way to go? |
|
|
|
|
how to get date of birth and age in years?