conver datetime to age... new

using the following query..
SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
the converts the table
userid birthdate
2 1985-03-08 00:00:00.000
9 1998-06-27 00:00:00.000
to the output of:
UserID Age
2 20
9 7
The problem is it doesn't take into consideration for the current day,
rather it looks only at the year. So the age of UserID 9 is actually 6.
-Thanks HP/Thomas on the other thread.http://groups-beta.google.com/group...mming&lr=&hl=en
"chad" <chad@.discussions.microsoft.com> wrote in message
news:1667E4FD-855D-4D1A-BFB9-2CFB0D456005@.microsoft.com...
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||See if this helps:
http://www.tech-archive.net/Archive...04-02/2296.html
AMB
"chad" wrote:

> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||I guess this is the easiest solution:
SELECT userid as UserID, Age = datediff(dd,Birthdate,getdate())/365
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
order by UserID
"chad" wrote:

> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Here's a formula I use:
Year(getdate())
- Year(birthdate)
+ case when datepart(dy, birthdate) - datepart(dy, getdate()) < 0
then 0 else -1 end
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlLtXoechKqOuFEgEQJ93gCgsWvUYstK/25OJhhdk7mUMSsdKqsAmwYF
rv4IxkY87JMffYj7v+i/Y31/
=AH6W
--END PGP SIGNATURE--
chad wrote:
> using the following query..
> SELECT userid as UserID, Age=datediff(year,Birthdate,getdate())
> FROM UserProfile
> WHERE UserProfile.Birthdate IS NOT NULL AND
> datediff(year,Birthdate,getdate())>=0
> order by UserID
> the converts the table
> userid birthdate
> 2 1985-03-08 00:00:00.000
> 9 1998-06-27 00:00:00.000
>
> to the output of:
> UserID Age
> 2 20
> 9 7
> The problem is it doesn't take into consideration for the current day,
> rather it looks only at the year. So the age of UserID 9 is actually 6.
> -Thanks HP/Thomas on the other thread.
>|||Yet another one:
datename(yy,getdate()-vt.birthday) - 1900