PLEASE HELP DATEDIFF(,,,) GET AGE FROM DATE AND NOW() or GETDA

Thanx All.
Can I Ask What If Their Borned on a leap year? would chandra's query still
be effective and exact?Hi
You can also try this:
SELECT DATEDIFF(d, Dob, GETDATE()) / 364 AS Years,
( 12 + ( datepart(m, dob) - datepart(m,getdate()))) % 12 AS Months
FROM Basic
WHERE (UName = @.UName)
the query will give u, age in years and months
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"eamon" wrote:

> Thanx All.
> Can I Ask What If Their Borned on a leap year? would chandra's query still
> be effective and exact?|||OMG. O thank you guys my boss is so please and impressed but im taking all
the credit. im just trying to make a dating site for the client and mite hav
e
some more queries for help. thank all of you so much. xxxx especially Chandr
a
and Roji . p .Thomas.
"Chandra" wrote:
> Hi
> You can also try this:
> SELECT DATEDIFF(d, Dob, GETDATE()) / 364 AS Years,
> ( 12 + ( datepart(m, dob) - datepart(m,getdate()))) % 12 AS Months
> FROM Basic
> WHERE (UName = @.UName)
> the query will give u, age in years and months
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "eamon" wrote:
>|||just a minor adjustment for anyone else who needs this post
correction to the orginal chandra wrote:
SELECT DATEDIFF(d, Dob, GETDATE()) / 364 AS Years,
( 12 - ( datepart(m, dob) - datepart(m,getdate()))) % 12 AS Months
FROM Basic
WHERE (UName = @.UName)
the other one is useful if you want to retrieve the months left but the one
above is if u want to retrieve the months that is current.
Thanx Chandra
"Chandra" wrote:
> Hi
> You can also try this:
> SELECT DATEDIFF(d, Dob, GETDATE()) / 364 AS Years,
> ( 12 + ( datepart(m, dob) - datepart(m,getdate()))) % 12 AS Months
> FROM Basic
> WHERE (UName = @.UName)
> the query will give u, age in years and months
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "eamon" wrote:
>|||Hi
good answer. and thank you for the correction
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"eamon" wrote:
> just a minor adjustment for anyone else who needs this post
> correction to the orginal chandra wrote:
> SELECT DATEDIFF(d, Dob, GETDATE()) / 364 AS Years,
> ( 12 - ( datepart(m, dob) - datepart(m,getdate()))) % 12 AS Months
> FROM Basic
> WHERE (UName = @.UName)
> the other one is useful if you want to retrieve the months left but the on
e
> above is if u want to retrieve the months that is current.
> Thanx Chandra
> "Chandra" wrote:
>|||No probs. I just got in and i must say you have been a very great help and i
learnt something new today. However, since your the most knowledgeable perso
n
i know can i ask another question. How do I make a stored procedure that
selects users in the table who are between the ages of 16 - 21, 22 - 34, 35
-
44, and so forth. i tried all sorts of stuff as i am new to sql and a little
bit of a try then read now later 18year old lol. thank you for all you have
taught me today, you really gave me hope in this career and the pay check :
D
hehehe thank you once again, sorry im so flirty.
"Chandra" wrote:
> Hi
> good answer. and thank you for the correction
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "eamon" wrote:
>|||You mean something like the following? If not post back with DDL+Sample
Data+Desired Results.
CREATE TABLE #Prospects(name VARCHAR(35), age int)
INSERT INTO #Prospects VALUES('Steve',22)
INSERT INTO #Prospects VALUES('Alby',18)
INSERT INTO #Prospects VALUES('MAry',15)
INSERT INTO #Prospects VALUES('Fred',26)
INSERT INTO #Prospects VALUES('Ben',32)
INSERT INTO #Prospects VALUES('Sam',38)
INSERT INTO #Prospects VALUES('Jane',20)
INSERT INTO #Prospects VALUES('Joe',50)
SELECT name,
CASE WHEN AGE <16 Then 'Less than 16'
WHEN AGE BETWEEN 16 AND 21 Then '16-22'
WHEN AGE BETWEEN 22 AND 34 Then '22-34'
WHEN AGE BETWEEN 35 AND 44 Then '35-44'
WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
FROM #Prospects
SELECT AgeGroup, COUNT(*)
FROM (
SELECT CASE WHEN AGE <16 Then 'Less than 16'
WHEN AGE BETWEEN 16 AND 21 Then '16-22'
WHEN AGE BETWEEN 22 AND 34 Then '22-34'
WHEN AGE BETWEEN 35 AND 44 Then '35-44'
WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
FROM #Prospects) T
GROUP By AgeGroup
DROP TABLE #Prospects
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"eamon" <eamon@.discussions.microsoft.com> wrote in message
news:21960A5D-D3C5-4842-8C8E-3BDA192532B1@.microsoft.com...
> No probs. I just got in and i must say you have been a very great help and
> i
> learnt something new today. However, since your the most knowledgeable
> person
> i know can i ask another question. How do I make a stored procedure that
> selects users in the table who are between the ages of 16 - 21, 22 - 34,
> 35 -
> 44, and so forth. i tried all sorts of stuff as i am new to sql and a
> little
> bit of a try then read now later 18year old lol. thank you for all you
> have
> taught me today, you really gave me hope in this career and the pay check
> :D
> hehehe thank you once again, sorry im so flirty.
> "Chandra" wrote:
>|||Thank you m8 i hope i can count on you guys. because im still learning how t
o
sql and i havent read anything on it at all just thought one day let me try
something and now my boss is please coz he likes the speed of the clients
websites compared to Ms Access thank you m8
"Roji. P. Thomas" wrote:

> You mean something like the following? If not post back with DDL+Sample
> Data+Desired Results.
>
> CREATE TABLE #Prospects(name VARCHAR(35), age int)
> INSERT INTO #Prospects VALUES('Steve',22)
> INSERT INTO #Prospects VALUES('Alby',18)
> INSERT INTO #Prospects VALUES('MAry',15)
> INSERT INTO #Prospects VALUES('Fred',26)
> INSERT INTO #Prospects VALUES('Ben',32)
> INSERT INTO #Prospects VALUES('Sam',38)
> INSERT INTO #Prospects VALUES('Jane',20)
> INSERT INTO #Prospects VALUES('Joe',50)
> SELECT name,
> CASE WHEN AGE <16 Then 'Less than 16'
> WHEN AGE BETWEEN 16 AND 21 Then '16-22'
> WHEN AGE BETWEEN 22 AND 34 Then '22-34'
> WHEN AGE BETWEEN 35 AND 44 Then '35-44'
> WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
> FROM #Prospects
> SELECT AgeGroup, COUNT(*)
> FROM (
> SELECT CASE WHEN AGE <16 Then 'Less than 16'
> WHEN AGE BETWEEN 16 AND 21 Then '16-22'
> WHEN AGE BETWEEN 22 AND 34 Then '22-34'
> WHEN AGE BETWEEN 35 AND 44 Then '35-44'
> WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
> FROM #Prospects) T
> GROUP By AgeGroup
>
> DROP TABLE #Prospects
>
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "eamon" <eamon@.discussions.microsoft.com> wrote in message
> news:21960A5D-D3C5-4842-8C8E-3BDA192532B1@.microsoft.com...
>
>|||OK i see how useful that query can be and i thank you for it. However, I don
t
know how to explain this but here goes because i haven't attemtped to create
the procedure as yet. table details are below as follows.
The Colums to be returned are below
SELECT Nick, Status, Sexuality, Gender, Ethnic, Zodiac, Dob, City, Country,
Uname
FROM Basic
Now The Dob = datetime
All the rest = nvarchar
Dob is the where the age is stored
this is just a basic try out
SELECT Nick, Status, Sexuality, Gender, Ethnic, Zodiac, Dob, City, Country,
Uname, CASE WHEN AGE < NOW() - 16yy Then 'Less than 16'
WHEN AGE BETWEEN 16 AND 21 Then '16-22'
WHEN AGE BETWEEN 22 AND 34 Then '22-34'
WHEN AGE BETWEEN 35 AND 44 Then '35-44'
WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
FROM Basic
Ok i tried but failed to get it to a working point. Is there a way i can use
your query but convert the values of all the users ages in int to go into a
new table with just their UName & Age. Then Return the details back from the
Basic using the values from the Age column in the new table where their UNam
e
property is attached so it returns the UName and then i will be able to get
all the details from the basic table where the Uname(s) are specified from
the new table. phew!! sorry i couldn't explain it.
Thank You
"Roji. P. Thomas" wrote:

> You mean something like the following? If not post back with DDL+Sample
> Data+Desired Results.
>
> CREATE TABLE #Prospects(name VARCHAR(35), age int)
> INSERT INTO #Prospects VALUES('Steve',22)
> INSERT INTO #Prospects VALUES('Alby',18)
> INSERT INTO #Prospects VALUES('MAry',15)
> INSERT INTO #Prospects VALUES('Fred',26)
> INSERT INTO #Prospects VALUES('Ben',32)
> INSERT INTO #Prospects VALUES('Sam',38)
> INSERT INTO #Prospects VALUES('Jane',20)
> INSERT INTO #Prospects VALUES('Joe',50)
> SELECT name,
> CASE WHEN AGE <16 Then 'Less than 16'
> WHEN AGE BETWEEN 16 AND 21 Then '16-22'
> WHEN AGE BETWEEN 22 AND 34 Then '22-34'
> WHEN AGE BETWEEN 35 AND 44 Then '35-44'
> WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
> FROM #Prospects
> SELECT AgeGroup, COUNT(*)
> FROM (
> SELECT CASE WHEN AGE <16 Then 'Less than 16'
> WHEN AGE BETWEEN 16 AND 21 Then '16-22'
> WHEN AGE BETWEEN 22 AND 34 Then '22-34'
> WHEN AGE BETWEEN 35 AND 44 Then '35-44'
> WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
> FROM #Prospects) T
> GROUP By AgeGroup
>
> DROP TABLE #Prospects
>
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "eamon" <eamon@.discussions.microsoft.com> wrote in message
> news:21960A5D-D3C5-4842-8C8E-3BDA192532B1@.microsoft.com...
>
>|||OK.
Read http://aspfaq.com/etiquette.asp?id=5006 carefully.
And post DDL (Table create script) + Sample Data (INSERT scripts) and
desired results.
I will try to help you.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"eamon" <eamon@.discussions.microsoft.com> wrote in message
news:4696CC96-3205-4EC1-BE61-894CB0EB5F71@.microsoft.com...
> OK i see how useful that query can be and i thank you for it. However, I
> dont
> know how to explain this but here goes because i haven't attemtped to
> create
> the procedure as yet. table details are below as follows.
> The Colums to be returned are below
> SELECT Nick, Status, Sexuality, Gender, Ethnic, Zodiac, Dob, City,
> Country,
> Uname
> FROM Basic
> Now The Dob = datetime
> All the rest = nvarchar
> Dob is the where the age is stored
> this is just a basic try out
> SELECT Nick, Status, Sexuality, Gender, Ethnic, Zodiac, Dob, City,
> Country,
> Uname, CASE WHEN AGE < NOW() - 16yy Then 'Less than 16'
> WHEN AGE BETWEEN 16 AND 21 Then '16-22'
> WHEN AGE BETWEEN 22 AND 34 Then '22-34'
> WHEN AGE BETWEEN 35 AND 44 Then '35-44'
> WHEN AGE > 44 THEN 'Greater than 44' END As AgeGroup
> FROM Basic
> Ok i tried but failed to get it to a working point. Is there a way i can
> use
> your query but convert the values of all the users ages in int to go into
> a
> new table with just their UName & Age. Then Return the details back from
> the
> Basic using the values from the Age column in the new table where their
> UName
> property is attached so it returns the UName and then i will be able to
> get
> all the details from the basic table where the Uname(s) are specified from
> the new table. phew!! sorry i couldn't explain it.
> Thank You
> "Roji. P. Thomas" wrote:
>sql