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,521 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

630 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