October 17, 2006 at 8:13 am
Afternoon,
I have a simple table with an attribute called BirthDate and I want another attribute called Agreed_Retirement_Date which defaults to the individuals 65th birthday. I have created a UDF (Generate_Retire_Date) using the dateadd function, which returns the desired result.
The Agreed_Retirement_Date attribute may be edited in the future to meet an individuals circumstances e.g. retiring at 68
My problem is using this UDF as the default value in the table, how do I enter my requirement into the "Design Table Screen". I know that this should be simple, but it just will not lay down and do what I tell it!
Thank you
Colin
October 17, 2006 at 8:28 am
From the top of my head :
RetierementDate is a smalldatetime column.
update all the rows to reflect the default retirement date
then RetierementDate defaults to dbo.Generate_Retire_Date(BirthDate)
October 17, 2006 at 8:39 am
In SQL2000, I do not think defaults are allowed to refer to other columns in the table.
You may be able to get away with this in SQL2005.
October 17, 2006 at 8:42 am
Thank you for the prompt reply.
However, if I enter dbo.Generate_Retire_Date(BirthDate) in "Default value" and try and save I get an error message saying "Error validating the default for column (Agreed_Retirement_Date)
The code for my UDF is
CREATE FUNCTION dbo.USR_Generate_Retirement_Date (@BirthDate datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @Agreed_Retirement_Date datetime
SET @Agreed_Retirement_Date = DATEADD(YEAR, 65, @BirthDate)
RETURN @Agreed_Retirement_Date
END
Colin
October 17, 2006 at 8:51 am
Ya I had forgotten that one. Looks like you'll have to set the default in the insert procedure, or in a trigger.
October 17, 2006 at 8:55 am
Thanks folks. I will have to sort this from a different angle. Anyway, it was good to experiment.
Cheers
Colin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply