alter table alter column with default value of getdate

Question

Post reply

alter table alter column with default value of getdate

AshaRRichardson2nd

Mr or Mrs. 500

Points: 504
More actions
March 16, 2009 at 3:49 pm

#207498

Why is the following not parsing?
ALTER TABLE [dbo].[strategy_card_definition]
ALTER COLUMN [create_date] SET DEFAULT getdate()

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

AshaRRichardson2nd Mr or Mrs. 500 Points: 504 More actions · Answer 1

this should actually be an add column datetime with a defaulte setting of getdate().

Also, how do I specify no check constraint?

Adiga One Orange Chip Points: 27284 More actions · Answer 2

You cannot use ALTER COLUMN to set the Defaults. The below query should work.

ALTER TABLE [dbo].[strategy_card_definition]
ADD CONSTRAINT DF_Date DEFAULT(GETDATE()) FOR [create_date]

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

AshaRRichardson2nd (3/16/2009)
this should actually be an add column datetime with a defaulte setting of getdate().
Also, how do I specify no check constraint?

If you want to add a new column with a default constraint (as I understood from your second message), you can use this code:

create table MyTable (MyInt int)

alter table MyTable add MyDate datetime

constraint DF_MyTable_MyDate default(getdate())

If you want to add a default constraint to a column that already exists, you can do it the way that Adiga wrote.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

AshaRRichardson2nd Mr or Mrs. 500 Points: 504 More actions · Answer 4

The customer has changed his mind. He wants all existing rows in the table populated with the default value once the column gets added. So I was doing something like this. It seems to work, but how do I get the NOT NULL in there?

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'type_of_fraud' AND COLUMN_NAME = 'matchfile_autoload')

BEGIN

ALTER TABLE [dbo].[type_of_fraud]

ADD [matchfile_autoload] char(01) NULL

END

--Step #5

begin TRAN

update dbo.type_of_fraud

set matchfile_autoload = 'N'

Commit TRAN

--Step #6

ALTER TABLE [dbo].[type_of_fraud]

ADD CONSTRAINT DF_No_matchfile_autoload DEFAULT('N') FOR [matchfile_autoload]

GO

Thanks, everyone!

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

As Adi said, you can add both column as well as constraint in a single ALTER statement:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'type_of_fraud' AND COLUMN_NAME = 'matchfile_autoload')

BEGIN

ALTER TABLE [dbo].[type_of_fraud]

ADD [matchfile_autoload] char(01) NOT NULL

CONSTRAINT DF_No_matchfile_autoload DEFAULT('N')

END

--Ramesh

AshaRRichardson2nd Mr or Mrs. 500 Points: 504 More actions · Answer 6

AshaRRichardson2nd

Mr or Mrs. 500

Points: 504

March 17, 2009 at 1:00 pm

#961319

Thank you! Worked perfectly.:-)

Ahmad Mahmood Newbie Points: 1 More actions · Answer 7

SQL SERVER

Set column type datetime default value getdate()

ALTER TABLE MyTable
ADD DEFAULT GETDATE() FOR MyColumn

Alter table add new colum with default getdate() function

ALTER TABLE MyTableADD
DateInserted DATETIME NOT NULL DEFAULT (GETDATE());

Attachments:

You must be logged in to view attached files.

alter table alter column with default value of getdate

Attachments:

Cookies on SQLServerCentral