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,332 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 42
PL/SQL query 14 50
Log Backup 2 13
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 17
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now