View Single Post
 
Old March 6th, 2006, 04:07 PM
nuttylife2 nuttylife2 is offline
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello, Thank you very much for that tip, the idea of creating a virtual field was great. But in the formula field it is saying the formula is wrong.
My date of birth is in varchar format "08/08/1965" so I tried using your function two ways, one changing the input in the function to varchar and then converting to datetime
  set @iAge = datediff(year,Convert(varchar(20),@dDOB,101),@dCur rentDate)
then I tried coverting it in the formula field as:(dbo.Calculateage([Convert(varchar(20),birthdate,101)],getdate()))
None of them are working.
What am I doing wrong? Please help.

Quote:
quote:Originally posted by JoeFlow
 You need to create the following User defined function in SQL and setup a virtual field in your table called Age (or whatever):
(Just create new function, paste the following in there and save)

create function CalculateAge(@dDOB datetime, @dCurrentDate datetime) returns int as
begin
declare @iAge int

set @iAge = datediff(year, @dDOB, @dCurrentDate)

-- See if birtdate is before current date.
-- If so, subtract one from age.

if @dCurrentDate > @dDOB
     set @iAge = @iAge - 1

return @iAge
end

Next, add a new field to your table called age.
In the "formula" property of the field do this:
(dbo.Calculateage([birthdate],getdate()))

That's it. Use the Age field in anything without ever having to calculate anything in any code.