Alter Column

Hi
How come this doesnt work?
ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.A default in SQL Server is handled as a constraint. So you would need something like:
ALTER TABLE tblname
ADD CONSTRAINT cnstname DEFAULT CURRENT_TIMESTAMP FOR columnname
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob Trask" <bob@.techset.net> wrote in message news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Bob,
You need to add a constraint to the column:
ALTER TABLE [tablename]
ADD CONSTRAINT <constraintname>
DEFAULT getdate() FOR [columnB]
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Bob Trask wrote:
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Bob
CREATE TABLE Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME NOT NULL
)
INSERT INTO Test VALUES (1,'20040101')
GO
ALTER TABLE Test ADD CONSTRAINT
myconst_col2 DEFAULT GETDATE() FOR col2
GO
INSERT INTO Test VALUES (2,DEFAULT)
GO
SELECT * FROM Test
GO
DROP TABLE Test
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>|||Cheers People, great help thanks!
"Bob Trask" <bob@.techset.net> wrote in message
news:uLy7qEctEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Hi
> How come this doesnt work?
>
> ALTER TABLE [tablename] ALTER COLUMN [columnB] SET DEFAULT getdate()
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'SET'.
>