March 27, 2009 at 6:43 am
Hello All-
I have a question regarding how I should go about something I need to put in place and I hope someone out there can assist me. I have a table with multiple columns, 2 of which hold dates called AppDate, and ContractDate. I need to update AppDate with ContractDate and set a constraint to update going forward. So basically the two columns need to coincide with one another.
My thoughts are that I can do this with a check constraint, a tigger, or even a SP. I think a check constraint would be the better option. I've tried creating one through the Management Studio GUI with no luck.
I've done tons of research but can't seem to dig anything up on this. Does anyone know how to go about doing this?
Thanks much!
March 27, 2009 at 8:13 am
Hi jc
A check constraint will limit what value can be inserted into a field, I am not sure this adresses your requirement correctly.
I suggest first running an update to sync your dates. After that you could make one of the two column a "calculated column".
Basically you can say your column equals the content of another column. You could use a more complex formula but I think this should work for you.
IF OBJECT_ID('tempdb..#Dates') IS NOT NULL
DROP TABLE #Dates
CREATE TABLE #Dates (
AppDate DATETIME
,ContractDate DATETIME
)
--==== Generate a bunch of dates
INSERT INTO #Dates
SELECT GETDATE(), GETDATE()+1
UNION ALL
SELECT GETDATE()+2, GETDATE()+3
UNION ALL
SELECT GETDATE()+4, GETDATE()+5
UNION ALL
SELECT GETDATE()+6, GETDATE()+7
--==== Validate content
SELECT * FROM #Dates
--==== Sync dates
UPDATE #Dates SET AppDate = ContractDate
--==== Validate content
SELECT * FROM #Dates
--==== Add a calculated column to demonstrate
ALTER TABLE #Dates
ADD CalculatedColumn AS ContractDate
--==== Validate content
SELECT * FROM #Dates
Let me know if this helps or if you have questions.
Maxim
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply