How do I write a trigger to calculate an age in years, based of the date of birth in the same table.

I have a database named FosterKids
I have a table called dbo.tbl_Basic_Info
I have two Columns DOB (datetime, null)
                                  Age (smallint, null)

What I want to do is create a trigger that when the DOB is keyed the database will calculate and save the age in years.

What's the coding that needs to be written? Is there alternative I'm not considering, if their is an alternative where, how, and what do I do?

Thanks
Jermey
OrderlyChoasAsked:
Who is Participating?
 
raulggonzalezConnect With a Mentor Commented:
Hi,

@acperkins is absolutely right about the use of DATEDIFF as will return a truncated value.

I wrote a custom function which calculates the real age from the date of birth. You can use it to create a computed column (my recommendation) or within a trigger.

Hope it helps. Cheers
CREATE FUNCTION [dbo].[getRealAge](@DOB DATE)
RETURNS SMALLINT  
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
	DECLARE @dayOB		SMALLINT
	DECLARE @monthOB	SMALLINT
	DECLARE @yearOB		SMALLINT

	DECLARE @dayC		SMALLINT
	DECLARE @monthC		SMALLINT
	DECLARE @yearC		SMALLINT
    
    DECLARE @realAge	SMALLINT

	SELECT	@dayOB		= DAY(@DOB), 
			@monthOB	= MONTH(@DOB),
			@yearOB		= YEAR(@DOB) 

	SELECT	@dayC		= DAY(GETDATE()), 
			@monthC		= MONTH(GETDATE()),
			@yearC		= YEAR(GETDATE()) 


	SET @realAge = @yearC - @yearOB

	IF @monthC - @monthOB < 0 OR @dayC - @dayOB < 0 SET @realAge = @realAge - 1 

    RETURN @realAge
END

GO

ALTER TABLE FosterKids.dbo.tbl_Basic_Info ADD ageInYears AS [dbo].[getRealAge](DOB) 
GO

Open in new window

0
 
Anthony PerkinsCommented:
>>What I want to do is create a trigger that when the DOB is keyed the database will calculate and save the age in years.<<
The problem with using a TRIGGER to update the table, is that it will be stale information, the minute you have completed the operation.  You would be better served putting that code in a computed column.

0
 
mimran18Commented:
Hi, Agreed with acperkins:.

but if you really need to do in that way.

Here is the script.

 
Drop table test
Go
Create table test
(ID int,
[DOB] nvarchar(50),
[Age] int default(0)
)
Go
CREATE TRIGGER Trg_UpdateDOB
ON test  
FOR INSERT
AS  
SET NOCOUNT ON; 
DECLARE @ID int
SELECT @ID =[ID] FROM INSERTED 

Update [test] set [Age]=DateDiff(year,[DOB],getdate())
Where [ID]=@ID

Go 



Insert  into test (ID,[DOB])values (1,'1989-12-12')
Insert  into test (ID,[DOB])values (2,'1990-12-12')
Insert  into test (ID,[DOB]) values (3,'1992-12-12')
GO



Select * from test

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Alpesh PatelAssistant ConsultantCommented:
Just Create Insert TRigger on table and

Use Select DOB from inserted table and update appropriate age using logic.

Update [test] set [Age]=DateDiff(year,[DOB],getdate())
Where [ID]=@ID
0
 
raulggonzalezCommented:
Hi,

I think that creating a trigger for such a task is a completely waste, maybe you can create a COMPUTED column instead, totally maintenance free...

ALTER TABLE FosterKids.dbo.tbl_Basic_Info ADD ageInYears AS (DATEDIFF(YYYY, DOB, GETDATE()))

I wrote the statement with other column name than yours.

If you finally want to implement it, you should DROP the existing column and re-create it again.

I think it worth it.


Cheers.
0
 
Anthony PerkinsCommented:
Unfortunately, it is obvious that no one has actually tried using DATEDIFF(year, DOB, GETDATE()) or they would know that solution is flawed.

Please check your solutions before you post them or at least acknowledge that you have not tried it.
0
 
Anthony PerkinsConnect With a Mentor Commented:
I realize most of you have just cracked the SQL Server books and got as far as the DATEDIFF() function, but let me give you an example.  Supposing your kid was born on December 25, 2010.  How old would he be on January 1, 2011?  When you have figured that out, test it with your code.  And test it also with Decemeber 31, 2010.
0
 
mimran18Commented:
Hi
acperkins: you are absolutly right.But most of the time we only deal with the year in age not days. That’s why we build it like this
But if he needs exact age it should be calculated on the basis of days.

Thank you for your feedback
0
 
deightonCommented:
also don't let anyone tell you that you can calculate datediff in days and divide by 365 or 365.25, also that can go wrong, whether you round down the answer or not.

0
 
OrderlyChoasAuthor Commented:
Thanks for your help with this, I've created a computed column rather than using a trigger.
0
 
Anthony PerkinsCommented:
mimran18
>>But if he needs exact age it should be calculated on the basis of days.<<
You are still not getting it, are you?  No one is suggesting that it be calculated in days, it just needs to be correct.  So once again I urge you to try out your code with my examples and see for yourself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.