[istore221]

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.

[thava]

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

[e_i_pi]

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)

[istore221]

thava, 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.

[thava]

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