October 23, 2008 at 4:20 am
I need to get the difference between two dates existing in the column and put the result into a new column. this change should be permanent to the table and Whenever I modify the dates in the table the the new difference column added should also get updated.
plz help me out with code
October 23, 2008 at 4:32 am
siddalingaswamyd (10/23/2008)
I need to get the difference between two dates existing in the column and put the result into a new column. this change should be permanent to the table and Whenever I modify the dates in the table the the new difference column added should also get updated.plz help me out with code
1. add a new column
2. create a insert trigger that will update the new col based on the dates difference
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 23, 2008 at 4:42 am
CREATE TRIGGER emp_trig ON humanresources.employee
after UPDATE
AS
INSERT INTO humanresources.employee
SELECT *,DATEDIFF(yy,birthdate,hiredate) YearsPassed FROM deleted
tried the above code but didnot work...
plz help me to correct the code to get the result properly
October 23, 2008 at 8:10 am
You may be better off forgetting about the trigger and using a computed column.
Something like:
ALTER TABLE humanresources.employee
ADD AgeWhenHired
AS YEAR(hiredate) - YEAR(birthdate)
    - CASE
        WHEN MONTH(hiredate) > MONTH(birthdate)
        THEN 0
        WHEN MONTH(hiredate) = MONTH(birthdate)
            AND DAY(hiredate) >= DAY(birthdate)
        THEN 0
        ELSE 1
    END
October 23, 2008 at 8:19 am
Computed column is the way to go
make it PERSIST if you can too
http://msdn.microsoft.com/en-us/library/ms191250.aspx
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
* Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.
* A computed column cannot be the target of an INSERT or UPDATE statement.
See if this does the trick
ALTER TABLE Table
ADD DateDiff AS (DATEDIFF(day, dateA, dateB)) PERSISTED
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply