Calculating and displaying "Age"

Most people don't consider that they are a certain age in years until AFTER the arrival of their birthdate. How can I calculate an "age" column in a table from a birthdate by rounding down instead of up?
alter table membe
add ag
as datediff(year,birth_date,getdate()Hi,
Since datediff with year will round up the value. Please use the below code,
alter table member
add age
as convert(int,datediff(month,birth_date,getdate())/12.0)
Thanks
Hari
MCDBA
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:08381C01-59BA-4517-852F-0B7445BBC66E@.microsoft.com...
> Most people don't consider that they are a certain age in years until
AFTER the arrival of their birthdate. How can I calculate an "age" column in
a table from a birthdate by rounding down instead of up?
> alter table member
> add age
> as datediff(year,birth_date,getdate())
>|||as DateDiff(year,birth_date,CURRENT_TIMESTAMP) - CASE
WHEN Month(birth_date) > Month(CURRENT_TIMESTAMP) THEN 1
WHEN Month(birth_date) = Month(CURRENT_TIMESTAMP)
AND Day(birth_date) > Day(CURRENT_TIMESTAMP) THEN 1
ELSE 0 END
Gert-Jan
Jack Wachtler wrote:
> Most people don't consider that they are a certain age in years until AFTER the arrival of their birthdate. How can I calculate an "age" column in a table from a birthdate by rounding down instead of up?
> alter table member
> add age
> as datediff(year,birth_date,getdate())
--
(Please reply only to the newsgroup)|||A few common ones:
--#1
SELECT DATEDIFF(yy, @.dob, CURRENT_TIMESTAMP) -
CASE WHEN (MONTH(CURRENT_TIMESTAMP) * 100 +
DAY(CURRENT_TIMESTAMP)) <
(MONTH(@.dob)* 100 + DAY(@.dob))
THEN 1 ELSE 0 END
--#2
SELECT (CAST(CONVERT(CHAR(8),CURRENT_TIMESTAMP, 112) AS INT)
- CAST(CONVERT(CHAR(8), @.dob, 112) AS INT))/10000
--#3
SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(@.dob) -
(CASE WHEN (MONTH(@.dob) > MONTH(CURRENT_TIMESTAMP))
OR (MONTH(@.dob) = MONTH(CURRENT_TIMESTAMP)
AND DAY(@.dob) > DAY(CURRENT_TIMESTAMP))
THEN 1 ELSE 0 END)
--
Anith|||http://www.aspfaq.com/2233
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:08381C01-59BA-4517-852F-0B7445BBC66E@.microsoft.com...
> Most people don't consider that they are a certain age in years until
AFTER the arrival of their birthdate. How can I calculate an "age" column in
a table from a birthdate by rounding down instead of up?
> alter table member
> add age
> as datediff(year,birth_date,getdate())
>|||We always use
Set @.Age=datediff(YYYY,@.DOB,@.SOMEDATE
-CASE WHE
CAST(CONVERT(CHAR(6),@.DOB,101)+CAST(DATEPART(YYYY,@.SOMEDATE) AS VARCHAR(4)) AS DATETIME
>@.SOMEDATE THEN 1 ELSE 0 EN|||Hari -- liked your simple solution the best and it works. Repeated below:
alter table membe
add ag
as convert(int,datediff(month,birth_date,getdate())/12.0
Thnx. -- Jack Wachtler|||Jack
You might like it, but it don't work..
print convert(int,datediff(month,'1990-02-06','1991-02-05')/12.0
gives me a 1 in QA - I believe this kid is one day short of a birthday
With the problem of rounding going on in DATEDIFF, I see no way of diff'ing the YEARS and seeing if the birthdate has physically passed by in the current year, and adjusting appropriately
Been doing it this fashion way before MS SQL came into the picture
Stev
-- Jack Wachtler wrote: --
Hari -- liked your simple solution the best and it works. Repeated below:
alter table membe
add ag
as convert(int,datediff(month,birth_date,getdate())/12.0
Thnx. -- Jack Wachtler