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 tab
le from a birthdate by rounding down instead of up?
alter table member
add age
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,ge
tdate())/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_TIMESTA
MP) - 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 AFTE
R the arrival of their birthdate. How can I calculate an "age" column in a t
able 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 WHEN
CAST(CONVERT(CHAR(6),@.DOB,101)+CAST(DATE
PART(YYYY,@.SOMEDATE) AS VARCHAR(4)) AS DATETIME)[co
lor=darkred]
>@.SOMEDATE THEN 1 ELSE 0 END[/color]|||Hari -- liked your simple solution the best and it works. Repeated below:
alter table member
add age
as convert(int,datediff(month,birth_date,ge
tdate())/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 curren
t year, and adjusting appropriately.
Been doing it this fashion way before MS SQL came into the picture.
Steve
-- Jack Wachtler wrote: --
Hari -- liked your simple solution the best and it works. Repeated below:
alter table member
add age
as convert(int,datediff(month,birth_date,ge
tdate())/12.0)
Thnx. -- Jack Wachtler|||Anith Sen wrote:

>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
>
>
Or for the lazy typist:
SELECT (0+CONVERT(CHAR(8),CURRENT_TIMESTAMP, 112)
- CONVERT(CHAR(8), @.dob, 112))/10000
SK

>--#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)
>
>|||This query will throw an error if the date of birth is on 29th of
February, and SomeDate is not a leap year...
Gert-Jan
Steve Z wrote:
> We always use:
> Set @.Age=datediff(YYYY,@.DOB,@.SOMEDATE)
> -CASE WHEN
> CAST(CONVERT(CHAR(6),@.DOB,101)+CAST(DATE
PART(YYYY,@.SOMEDATE) AS VARCHAR(4
)) AS DATETIME)
(Please reply only to the newsgroup)