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
AL开发者_Python百科TER COLUMN [__INSERT_DATE] [datetime] DEFAULT (getdate()) NULL)

Can anyone tell me what the problem is?

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

Try this:

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

ALTER TABLE sample ADD INSERT_DATE [datetime] NOT NULL DEFAULT GetDate()

This will works in Microsoft SQL Server

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()

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()

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 -> )

(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

This way worked for me:

ALTER TABLE sample ALTER COLUMN INSERT_DATE [datetime] NULL DEFAULT GetDate()

If you already have values in the column, you can do something like this:

Alter Table sample add INSERT_DATE_TEMP [datetime] NOT NULL DEFAULT GetDate()
Update sample
SET INSERT_DATE_TEMP = INSERT_DATE
Alter Table sample Drop Column INSERT_DATE
exec sp_rename 'sample.INSERT_DATE_TEMP','INSERT_DATE','COLUMN'

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