Default getdate for Insert date

问题:

I have a table called sample and it has a column called [__INSERT_DATE] which is null. Now I want to alter the column with default as getdate(). When I tried the following it gave me an error.

ALTER TABLE sample
ALTER COLUMN [__INSERT_DATE] [datetime] DEFAULT (getdate()) NULL)

Can anyone tell me what the problem is?

回答1:

Try this:

ALTER TABLE MyTable ADD CONSTRAINT
DF_MyTable_Inserted DEFAULT GETDATE() FOR INSERT_DATE
GO

This assumes your table is named MyTable, the column is INSERT_DATE, and the name of the contstraint is to be DF_MyTable_Inserted

回答2:

Try this:

ALTER TABLE sample ADD CONSTRAINT DF_sample___INSERT_DATE DEFAULT(GETDATE()) FOR __INSERT_DATE

回答3:

MSDN gives this example:

ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

That would give you

  ALTER TABLE sample ALTER COLUMN __INSERT_DATE SET DEFAULT GETDATE()

回答4:

ALTER TABLE sample ALTER COLUMN [__INSERT_DATE] [datetime] DEFAULT (getdate()) NULL)

You have one too many closing brackets in the above statement. There are 2 of these -> ( but 3 of these -> )

回答5:

Does getdate() return the correct date and time datatype for your declared column? There are some new date and time datatypes in SQL Server 2008.

Here's an article that explains some of the differences.

http://www.sql-server-performance.com/articles/dev/datetime_2008_p1.aspx

回答6:

I was able to do it using SSManagement Studio

make the date field not nullable, then from properties set Defalut Value or Binding to getdate()

回答7:

(Your_Date_Column) Make it Null / Not Null and give default value GetDate() but still it will not work. You have to create a trigger like this,

CREATE TRIGGER [dbo].[Trigger_Date] ON [dbo].[TableName] FOR INSERT AS BEGIN

    Declare @Id int
    set @Id = (select Id from inserted)

    Update [dbo].[TableName]
    Set Your_Date_Column = GetDate()
    Where Id = @Id
END