|
|
You're in the
sql server new users
group:Newbie: Help with T-SQL
sql server new users:
would like to modify the EndDate. Here's the table created. CREATE TABLE Events ( EventID int IDENTITY(1,1) NOT NULL, EventType nvarchar(10) NOT NULL Default 'Party', EventTitle nvarchar(100) NULL, EventDescription ntext NULL, EventLanguage nvarchar (2) NULL, EventDate smalldatetime NULL DEFAULT GETDATE(), EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 1, GETDATE()), EventCreator nvarchar (50) NOT NULL DEFAULT SYSTEM_USER) I would like to alter the EventEndDate column to EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 5, GETDATE()) I have tried Alter Table Events Alter column EventEndDate smalldatetime NOT NULL DEFAULT DATEADD(day, 5, GETDATE()) I get the error : Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'DEFAULT'. Please advise, Kaimuri
That's what happens when you don't name your constraints. Run
sp_helpconstraint to get the name of your constraint: sp_helpconstraint Events Pick out the relevant default and drop it: alter table events drop constraint DF__Events__EventEnd__61130711 go Next, add in the correct default: alter table Events add constraint DF1_Events default (DATEADD(day, 5, GETDATE())) for EventEndDate go -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com .. [quoted text, click to view] "Kaimuri" <kaimuri@somewhere.com> wrote in message I have created a table called events with default Start and End Dates. Inews:eFN2Ptk1EHA.2112@TK2MSFTNGP15.phx.gbl... would like to modify the EndDate. Here's the table created. CREATE TABLE Events ( EventID int IDENTITY(1,1) NOT NULL, EventType nvarchar(10) NOT NULL Default 'Party', EventTitle nvarchar(100) NULL, EventDescription ntext NULL, EventLanguage nvarchar (2) NULL, EventDate smalldatetime NULL DEFAULT GETDATE(), EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 1, GETDATE()), EventCreator nvarchar (50) NOT NULL DEFAULT SYSTEM_USER) I would like to alter the EventEndDate column to EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 5, GETDATE()) I have tried Alter Table Events Alter column EventEndDate smalldatetime NOT NULL DEFAULT DATEADD(day, 5, GETDATE()) I get the error : Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'DEFAULT'. Please advise, Kaimuri
Thanks..that worked..now I'll get into the habit of naming all my
constraints. It makes life a lot easier. [quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23pfbU7n1EHA.1408@TK2MSFTNGP10.phx.gbl... > That's what happens when you don't name your constraints. Run > sp_helpconstraint to get the name of your constraint: > > sp_helpconstraint Events > > Pick out the relevant default and drop it: > > alter table events drop constraint DF__Events__EventEnd__61130711 > go > > Next, add in the correct default: > > alter table Events add constraint DF1_Events default (DATEADD(day, 5, > GETDATE())) for EventEndDate > go > > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinnaclepublishing.com > . > "Kaimuri" <kaimuri@somewhere.com> wrote in message > news:eFN2Ptk1EHA.2112@TK2MSFTNGP15.phx.gbl... > I have created a table called events with default Start and End Dates. I > would like to modify the EndDate. Here's the table created. > > CREATE TABLE Events > > ( > > EventID int IDENTITY(1,1) NOT NULL, > > EventType nvarchar(10) NOT NULL Default 'Party', EventTitle nvarchar(100) > NULL, EventDescription ntext NULL, EventLanguage nvarchar (2) NULL, > EventDate smalldatetime NULL DEFAULT GETDATE(), EventEndDate smalldatetime > NULL DEFAULT DATEADD(day, 1, GETDATE()), EventCreator nvarchar (50) NOT > NULL > DEFAULT SYSTEM_USER) > > I would like to alter the EventEndDate column to > > EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 5, GETDATE()) > > I have tried > > Alter Table Events > > Alter column EventEndDate smalldatetime NOT NULL DEFAULT DATEADD(day, 5, > > GETDATE()) > > I get the error : Server: Msg 156, Level 15, State 1, Line 2 Incorrect > syntax near the keyword 'DEFAULT'. > > Please advise, > > Kaimuri > >
You got that right!
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com .. [quoted text, click to view] "Kaimuri" <kaimuri@somewhere.com> wrote in message Thanks..that worked..now I'll get into the habit of naming all mynews:%232R1MbK3EHA.3596@TK2MSFTNGP12.phx.gbl... constraints. It makes life a lot easier. [quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23pfbU7n1EHA.1408@TK2MSFTNGP10.phx.gbl... > That's what happens when you don't name your constraints. Run > sp_helpconstraint to get the name of your constraint: > > sp_helpconstraint Events > > Pick out the relevant default and drop it: > > alter table events drop constraint DF__Events__EventEnd__61130711 > go > > Next, add in the correct default: > > alter table Events add constraint DF1_Events default (DATEADD(day, 5, > GETDATE())) for EventEndDate > go > > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinnaclepublishing.com > . > "Kaimuri" <kaimuri@somewhere.com> wrote in message > news:eFN2Ptk1EHA.2112@TK2MSFTNGP15.phx.gbl... > I have created a table called events with default Start and End Dates. I > would like to modify the EndDate. Here's the table created. > > CREATE TABLE Events > > ( > > EventID int IDENTITY(1,1) NOT NULL, > > EventType nvarchar(10) NOT NULL Default 'Party', EventTitle nvarchar(100) > NULL, EventDescription ntext NULL, EventLanguage nvarchar (2) NULL, > EventDate smalldatetime NULL DEFAULT GETDATE(), EventEndDate smalldatetime > NULL DEFAULT DATEADD(day, 1, GETDATE()), EventCreator nvarchar (50) NOT > NULL > DEFAULT SYSTEM_USER) > > I would like to alter the EventEndDate column to > > EventEndDate smalldatetime NULL DEFAULT DATEADD(day, 5, GETDATE()) > > I have tried > > Alter Table Events > > Alter column EventEndDate smalldatetime NOT NULL DEFAULT DATEADD(day, 5, > > GETDATE()) > > I get the error : Server: Msg 156, Level 15, State 1, Line 2 Incorrect > syntax near the keyword 'DEFAULT'. > > Please advise, > > Kaimuri > >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |