Trigger to update column..I think!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to update column..I think!

Author

Topic

InNomina
Starting Member

40 Posts

Posted - 2011-09-29 : 18:32:54

I need to update a Destruction Date field based on someones age.

If 21 or older I need to add 13 years to the Start date and put the sum in the Destruction date field.

If under 20 or younger then I need to add 10 years in the same way.

Example: start date = 1/1/2000, Date of Birth = 2/2/1990 Destruction date = 1/1/2013

I have table A with column 1,2,3

Column 1 = start date
column 2 = date of birth
Column 3 = destruction date

I was practicing with a select statement first and got this...
SELECT CASE
WHEN DATEADD(YEAR,21,CAST(DOB AS DATETIME)) < getdate() --MINOR
THEN DATEADD(YEAR,13,CAST(DOB AS DATETIME)) ELSE DATEADD(YEAR,10,CAST(DOB AS DATETIME)) END,DOB
FROM table

But not sure how to turn this into a Select statment to make it function.

Please note: I will also need this to run nightly against any new records that are added that day.

-------------------------
"If you never fail, you're not trying hard enough"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-29 : 18:58:27

I don't think you need a function, just an update statement

INSERT INTO @table
 values('20000101','19900101',null)


UPDATE @table
set col3 = CASE WHEN DATEDIFF(day,col2,col1)/365 >= 21
                THEN DATEADD(year,10,col1)
                ELSE DATEADD(year,13,col1)
            END
            
 select * from @table

The reason I did this
DATEDIFF(day,col2,col1)/365

instead of DATEDIFF(year,Col2,Col1) is so that someone born on Dec 31
isn't 1 year old on January 1st.

Jim

Everyday I learn something that somebody else already knew

InNomina
Starting Member

40 Posts

Posted - 2011-09-29 : 19:29:59

This worked perfect!
Thank you very much!

quote:
Originally posted by jimf

I don't think you need a function, just an update statement

INSERT INTO @table
values('20000101','19900101',null)

UPDATE @table
set col3 = CASE WHEN DATEDIFF(day,col2,col1)/365 >= 21
THEN DATEADD(year,10,col1)
ELSE DATEADD(year,13,col1)
END

select * from @table

The reason I did this
DATEDIFF(day,col2,col1)/365

instead of DATEDIFF(year,Col2,Col1) is so that someone born on Dec 31
isn't 1 year old on January 1st.

Jim

Everyday I learn something that somebody else already knew

-------------------------
"If you never fail, you're not trying hard enough"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources