Solved

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

Posted on 2011-09-18
11
1,370 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:OrderlyChoas
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36557713
>>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
 
LVL 9

Expert Comment

by:mimran18
ID: 36558222
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36558384
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 8

Expert Comment

by:raulggonzalez
ID: 36559209
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36559559
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 36559587
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
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 400 total points
ID: 36559893
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
 
LVL 9

Expert Comment

by:mimran18
ID: 36559946
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
 
LVL 18

Expert Comment

by:deighton
ID: 36560479
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
 

Author Closing Comment

by:OrderlyChoas
ID: 36561184
Thanks for your help with this, I've created a computed column rather than using a trigger.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36561597
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question