convert datetime to age

I have used the following query to get an age from datetime:
select age
from (select case when dateadd(year, datediff(year, birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate())
-1 else datediff(year, birthdate, getdate()) end as age
from userprofile) as age
I'm trying to get two fields from table userprofile (userid, birthdate) and
viewed as
userid and the age.
gone blank on trying to figure out how to implement the previous query with
mulitple fields from a table.Why are you using a subquery?
select userid, birthdate as age case when dateadd(year, datediff(year,
birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
datediff(year, birthdate, getdate()) end as age
from userprofile
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.|||Select DateDiff(yy, BirthDate, @.TargetDate)
- Case
When Cast(
Cast(DatePart(yyyy, BirthDate) As Char(4))
+ Right('00' + Cast(DatePart(mm, @.TargetDate) As
VarChar(2)), 2)
+ Right('00' + Cast(DatePart(dd, @.TargetDate) As
VarChar(2)), 2) As SmallDateTime)
< @.TargetDate
Then 1
Else 0
End
From Table
Where @.TargetDate is the date you are comparing against. You could replace
this with GetDate() if you simply wanted their age as of now.
The second part creates an ISO date (yyyymmdd) in the year of the target
date for the month and day of the birth date. If the birth day in the target
year has not transpired, it subtracts one from the total. This technique is
impervious to leap years (well, as impervious as SQL's date interpretation)
and ensures that the value that comes out is one that a human would
interpret as the correct age.
Thomas
"chad" <chad@.discussions.microsoft.com> wrote in message
news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate)
> and
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query
> with
> mulitple fields from a table.|||the original query was for age conversion only and it worked well.
there was syntax errors in your query, where's the THEN expression.
appreciate the help.
"HP" wrote:
> Why are you using a subquery?
> select userid, birthdate as age case when dateadd(year, datediff(year,
> birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
> datediff(year, birthdate, getdate()) end as age
> from userprofile
>
> "chad" wrote:
>|||errors out at row 2893 out of 9998.
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
"Thomas C" wrote:

> Select DateDiff(yy, BirthDate, @.TargetDate)
> - Case
> When Cast(
> Cast(DatePart(yyyy, BirthDate) As Char(4))
> + Right('00' + Cast(DatePart(mm, @.TargetDate) As
> VarChar(2)), 2)
> + Right('00' + Cast(DatePart(dd, @.TargetDate) As
> VarChar(2)), 2) As SmallDateTime)
> < @.TargetDate
> Then 1
> Else 0
> End
> From Table
> Where @.TargetDate is the date you are comparing against. You could replace
> this with GetDate() if you simply wanted their age as of now.
> The second part creates an ISO date (yyyymmdd) in the year of the target
> date for the month and day of the birth date. If the birth day in the targ
et
> year has not transpired, it subtracts one from the total. This technique i
s
> impervious to leap years (well, as impervious as SQL's date interpretation
)
> and ensures that the value that comes out is one that a human would
> interpret as the correct age.
>
> Thomas
>
> "chad" <chad@.discussions.microsoft.com> wrote in message
> news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>
>|||Figured it out:
SELECT Age=datediff(year,Birthdate,getdate()),
userid
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
ORDER BY userid
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.