View Single Post
 
Old March 4th, 2006, 01:29 PM
JoeFlow JoeFlow is offline
Registered User
 
Join Date: Mar 2006
Location: Nashville, tn, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.