control but the problem is that DATEPART(). I can only Specify one and i need
the age to to the exact format of mm dd yy but i cant get it into the Query
so it can execute. And when I use the yyyy to return the Age it returns the
age as between 1 - 11 and between 1 - 364 days. So for example the user was
borned 21.11.85. when executed it returns the user is 20 when their 20th is
4months away.
<SQL QUERY>
SELECT DATEDIFF(yyyy, Dob, GETDATE()) AS Age
FROM Basic
WHERE (UName = @dotnet.itags.org.UName)This is because using "yyyy" with the DATEDIFF Transact-SQL function to get
the difference in dates subtracts only the year value. One solution is to
simply select the Date from the database, and use the DateTime.Subtract
method to get a TimeSpan indicating the difference between today and the
date from the database. The Years component will give you the age.
--
HTH,
Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.
"eamon" <eamon@.discussions.microsoft.com> wrote in message
news:79683054-0256-40E7-9CF3-5AA59AA927BD@.microsoft.com...
> Im making this stored procedure to return the age of the user to the web
> control but the problem is that DATEPART(). I can only Specify one and i
> need
> the age to to the exact format of mm dd yy but i cant get it into the
> Query
> so it can execute. And when I use the yyyy to return the Age it returns
> the
> age as between 1 - 11 and between 1 - 364 days. So for example the user
> was
> borned 21.11.85. when executed it returns the user is 20 when their 20th
> is
> 4months away.
> <SQL QUERY>
> SELECT DATEDIFF(yyyy, Dob, GETDATE()) AS Age
> FROM Basic
> WHERE (UName = @.UName)
hi
looks like this is a repeated post. here is the answer with the correction
that u made
SELECT DATEDIFF(d, Dob, GETDATE()) / 364 AS Years,
( 12 - ( datepart(m, dob) - datepart(m,getdate()))) % 12 AS Months
FROM Basic
WHERE (UName = @.UName)
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
------------
"eamon" wrote:
> Im making this stored procedure to return the age of the user to the web
> control but the problem is that DATEPART(). I can only Specify one and i need
> the age to to the exact format of mm dd yy but i cant get it into the Query
> so it can execute. And when I use the yyyy to return the Age it returns the
> age as between 1 - 11 and between 1 - 364 days. So for example the user was
> borned 21.11.85. when executed it returns the user is 20 when their 20th is
> 4months away.
> <SQL QUERY>
> SELECT DATEDIFF(yyyy, Dob, GETDATE()) AS Age
> FROM Basic
> WHERE (UName = @.UName)
http://www.databasejournal.com/feat...cle.php/1459151
"eamon" <eamon@.discussions.microsoft.com> wrote in message
news:79683054-0256-40E7-9CF3-5AA59AA927BD@.microsoft.com...
> Im making this stored procedure to return the age of the user to the web
> control but the problem is that DATEPART(). I can only Specify one and i
> need
> the age to to the exact format of mm dd yy but i cant get it into the
> Query
> so it can execute. And when I use the yyyy to return the Age it returns
> the
> age as between 1 - 11 and between 1 - 364 days. So for example the user
> was
> borned 21.11.85. when executed it returns the user is 20 when their 20th
> is
> 4months away.
> <SQL QUERY>
> SELECT DATEDIFF(yyyy, Dob, GETDATE()) AS Age
> FROM Basic
> WHERE (UName = @.UName)
