4 Replies - 3162 Views - Last Post: 30 September 2012 - 10:05 PM

#1 istore221  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 136
  • Joined: 11-December 11

Auto Calculate Age From DOB column (Daily)

Posted 27 September 2012 - 09:23 PM

I Wan to do something like this..i have a table call Students Table..this is my structure

CREATE TABLE Students
(

Student_ID INT IDENTITY,
[First Name] VARCHAR(35),
[Middle Name] VARCHAR(35),
[Last Name] VARCHAR(35),
DOB DATE,
Age AS DATEDIFF(yyyy,DOB,GETDATE()) -- Computed Column Auto Genarate Age Based on BirthDay when inserting

CONSTRAINT [Primary Key For Students Table] PRIMARY KEY(Student_ID),

)

When i insert a record to the student table Age will be Automatically Calculate and store in Age Field.assume that i have 10 records in student table.

this is my prob :(
i want to calculate age of all 10 students every day and automatically update age field accoding to their DOB.in simple word student table (Age) Field should be automatically genarate age for each student every day.how can i do this.

Is This A Good Question/Topic? 0
  • +

Replies To: Auto Calculate Age From DOB column (Daily)

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Auto Calculate Age From DOB column (Daily)

Posted 27 September 2012 - 09:58 PM

what do you mean calculating the age daily?

did you calculate the age based on days, elaborate please?


any way Create a job in sql server 2005 and use the update statement
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Auto Calculate Age From DOB column (Daily)

Posted 27 September 2012 - 10:18 PM

Computed fields auto update, so your syntax should be correct. The problem is, you're comparing the year dateparts, meaning it's calculating the difference between the years, not the datetimestamps. In other words, '31-Dec-2011' and '01-Jan-2012' have a DATEDIFF of 1 in the year datepart. Try this instead for your calculation:
FLOOR(DATEDIFF(dd, DOB, GETDATE())/365.242)


Was This Post Helpful? 0
  • +
  • -

#4 istore221  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 136
  • Joined: 11-December 11

Re: Auto Calculate Age From DOB column (Daily)

Posted 28 September 2012 - 01:16 AM

View Postthava, on 27 September 2012 - 09:58 PM, said:

what do you mean calculating the age daily?

did you calculate the age based on days, elaborate please?


any way Create a job in sql server 2005 and use the update statement


when i insert a record in to table age will automatically calculate and stored in age field.but think..when i insert a record person A DOB is 1990-04-16 WHERE today Date 2012-04-15 in that case age will calculate as 21 ..but next day is 2012-04-16 WHERE Age of Person A is 22.but database have only Age 21.it is never updated.how can i update age field daily.
Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Auto Calculate Age From DOB column (Daily)

Posted 30 September 2012 - 10:05 PM

well this is why we need more details , if you search over the net you have found a lot of solutions any way this one is my favorite

DECLARE @DOB AS DATETIME
DECLARE @Dt2 AS DATETIME, @Dt3 AS DATETIME
SELECT @DOB = '2000-02-29', @dt2 = '2012-02-28', @Dt3 = '2012-02-29'

SELECT CASE 
            WHEN DATEADD( YY, DATEDIFF( YY, @DOB, @DT2 ), @DOB ) > @DT2 THEN 
                 DATEDIFF( YY, @DOB, @DT2 ) - 1
            ELSE DATEDIFF( YY, @DOB, @DT2 )
       END
SELECT CASE 
            WHEN DATEADD( YY, DATEDIFF( YY, @DOB, @Dt3 ), @DOB ) > @DT3 THEN 
                 DATEDIFF( YY, @DOB, @DT3 ) - 1
            ELSE DATEDIFF( YY, @DOB, @DT3 )
       END


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1