# Auto Calculate Age From DOB column (Daily)

Page 1 of 1

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

### #1 istore221

Reputation: 0
• 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

• D.I.C Lover

Reputation: 180
• 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

### #3 e_i_pi

• = -1

Reputation: 822
• Posts: 1,748
• 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)

```

### #4 istore221

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

## Re: Auto Calculate Age From DOB column (Daily)

Posted 28 September 2012 - 01:16 AM

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.

### #5 thava

• D.I.C Lover

Reputation: 180
• 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

```