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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-06 : 09:10:49
|
| Brian writes "OK - This will most likely be easy for the guru's here, but has me stumped.... I have a 'birthdate' field in my table and I am wanting to make a field named 'age' that will take (current_Date - birthdate)obviously... I am sure you are laughing now - but I can not remember the correct code to do this....Thanks for the help.. : )BS" |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-05-06 : 09:48:30
|
| I concur. Rob Volk's last solution in that thread is much the best way: it's far faster than anything that converts dates into strings, and it deals with birthdays on 29th Feb correctly. |
 |
|
|
tan_bum
Starting Member
5 Posts |
Posted - 2002-05-06 : 15:49:01
|
| I use the following for caculating age:select floor(datediff(dd, '06/27/1977', '02/08/2001')/365.25)tan_bum...in warm and sunny Florida |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-05-06 : 17:04:46
|
| I wouldn't. |
|
|
|
tan_bum
Starting Member
5 Posts |
Posted - 2002-05-07 : 09:56:26
|
| Mr. Fribble...As you can see, I am starting member...still learning this stuff. I would be interested in understanding a little bit about the pro's/con's of each method. Mind elaborating? Thank.tan_bum...in warm and sunny Florida |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-07 : 12:00:53
|
| Well, one problem with it is that it doesn't work for the following:select floor(datediff(dd, '06/27/1977', '06/27/1978')/365.25) --should return 1, but returns 0select floor(datediff(dd, '06/27/1977', '06/27/1979')/365.25) --should return 2, but returns 1select floor(datediff(dd, '06/27/1977', '06/27/1981')/365.25) --returns 4 yay! select floor(datediff(dd, '06/27/1977', '06/27/1982')/365.25) --returns 4 ???It works AS LONG AS a leap year intervenes between the 2 dates, and even then it will cause gaps in other sequences. Not to mention that leap years do not occur in a new century unless the century is divisible by 400 (2000 is, but 1900 is not, and there was no Feb. 29 1900)If you need to perform date arithmetic, use the DateDiff() and other date functions. The calendar is too screwed up to work with regular math. |
|
|
|
|
|
|
|
|
Birthdate to age conversion