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

Constructing a field name

Can a field name be constructed, and if so, how?
For example;
Declare @.Date SmallDateTime
Set @.Date = GetDate()
I would like to create a field called 2005_Sales in a temp table
called 2005_Sales by constructing it from:
Convert( char(4), year( @.Date) ) + '_Sales'
If a field can not be created can a field be aliased by the same
construction?
Any help would be appreciated.>> Any help would be appreciated. <<
You are supposed to know to know what a thing is before you do it.
Even more basic, a field is NOT ANYTHING WHATSOEVER like a column.
You are trying to mimic a 1950's file system report in SQL.
Please, please, please learn the basics of data modeling before you
kill people or bankrupt companies. This is the kidn of thing which you
woudl learn in the first two days of a course.