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,358 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

862 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

26 Experts available now in Live!

Get 1:1 Help Now