PLEASE HELP GET AGE FROM DATE

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 = @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)

PLEASE HELP GET AGE FROM DATE

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 nee
d
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 n
eed
> the age to to the exact format of mm dd yy but i cant get it into the Quer
y
> so it can execute. And when I use the yyyy to return the Age it returns th
e
> age as between 1 - 11 and between 1 - 364 days. So for example the user wa
s
> borned 21.11.85. when executed it returns the user is 20 when their 20th i
s
> 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)

Please Help me

Below code is giving me error like ''function doesnt return in all code paths A null reference exception could occur"

As i m new to this tech i am not getting that

PublicSharedFunction getLookupCampusList(ByVal eidAsString, _

ByVal isAdminAsBoolean)As SqlDataReader

'Dim getLookup As SqlDataReader = Nothing

Dim paraValueAsObject() = {eid}

Dim myRoleAsString = appRole(eid)

If isAdminThen

If myRole ="A"Then

Try

Return SQLHelper.ExecuteReader("sp_locationDes", paraValue)

Catch exAs Exception

Throw ex

EndTry

ElseIf myRole ="E"Then

Try

Return SQLHelper.ExecuteReader("sp_GetUsingCommonDB", paraValue)

Catch exAs Exception

Throw ex

EndTry

ElseIf myRole ="B"Then

Try

Return SQLHelper.ExecuteReader("sp_getByEmpLoc", paraValue)

Catch exAs Exception

Throw ex

EndTry

EndIf

Else

Try

Return SQLHelper.ExecuteReader("sp_GetEmpID", paraValue)

Catch exAs Exception

Throw ex

EndTry

EndIf

End Function

If you indent your code to highlight the control-flow, logic errors become easier to find.

This error means that the compiler found a logic path that did not make use of a return statement.

If a function says it returns a given object type, unless it exits via an unhandled exception, it must return that object type.

If isAdminThen
If myRole ="A"Then
Return SQLHelper.ExecuteReader("sp_locationDes", paraValue)
ElseIf myRole ="E"Then
Return SQLHelper.ExecuteReader("sp_GetUsingCommonDB", paraValue)
ElseIf myRole ="B"Then
Return SQLHelper.ExecuteReader("sp_getByEmpLoc", paraValue)
EndIf
Else
Return SQLHelper.ExecuteReader("sp_GetEmpID", paraValue)
EndIf

I removed your try-catch commands for two reasons. The first reason was to make the above code snippet easier to read.

What will be returned if isAdmin is true and myRole = "NotInThisList"?

And if you say to yourself, but, "NotInThisList" isn't a valid value for myRole, remember that the compiler does not know that. :)

So, you need to add a logic path to handle some myRole value other than A, E, or B, or you need to change the ElseIf on "B" to be an Else.

The second reason I removed your try catch commands were that they were redundant. They did nothing but catch an exception and re-throw it.

If you never bothered to do that you would get the same result - an exception that needs to be handled by the calling routine. So, either add useful code that fixes the exception or logs it into your catch block, or don't bother to put them in.

Thanks Alot Its working

Glad it helped! Now you need to mark the answer so others don't wander in here to help you when you no longer need it. :)