我想向表中添加一个新的createdtime列。但是,我希望表中所有以前的条目都显示"19700101"日期,而从这一点开始(即在引入新列之后(的所有新条目都应该使用getDate((显示当前日期
@Roopesh的评论做得很好:
create table T (ID int not null,Col1 varchar(9) not null)
go
insert into T(ID,Col1) values (1,'abc'),(2,'def')
go
alter table T add CreatedDate date not null constraint DF_tmp default '19700101'
go
alter table T drop constraint DF_tmp
go
alter table T add constraint DF_Permanent
DEFAULT CURRENT_TIMESTAMP for CreatedDate
go
insert into T(ID,Col1) values (3,'ghi')
go
select * from T
结果是:
ID Col1 CreatedDate
----------- --------- -----------
1 abc 1970-01-01
2 def 1970-01-01
3 ghi 2014-06-13
这具有列不允许NULL
s的优点。如果使用Mikael的方法,则在应用了UPDATE
之后,可以使列不可为null,但这需要对表进行两次扫描(一次应用UPDATE
,第二次在创建列not null
时检查任何NULL
s(。
首先添加一个具有默认值getdate()
的列
alter table YourTable add createddate date default getdate()
此步骤中不应用默认值。每一行都有null
作为createddate
的值。
将所有行的createddate
的值更新为1970-01-01
。
update YourTable set createddate = '1970-01-01'
--Create Table
CREATE TABLE [dbo].[MyTestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
) ON [PRIMARY]
GO
--Put in some data
INSERT INTO [MyTestTable] (Name) VALUES ('Eoin')
INSERT INTO [MyTestTable] (Name) VALUES ('Living Thing')
GO
-- Add a new column datetime with a default value of min date
ALTER TABLE dbo.MyTestTable ADD
CreatedDate datetime NOT NULL CONSTRAINT DF_MyTestTable_CreatedDate DEFAULT Convert(Datetime, '19700101')
GO
-- Existing Records are now updated to Jan 1970
SELECT * FROM MyTestTable
GO
-- Alter the table again but with a new default
ALTER TABLE dbo.MyTestTable
DROP CONSTRAINT DF_MyTestTable_CreatedDate
GO
ALTER TABLE dbo.MyTestTable ADD CONSTRAINT
DF_MyTestTable_CreatedDate DEFAULT getdate() FOR CreatedDate
GO
--Put in some more data.
INSERT INTO [MyTestTable] (Name) VALUES ('John')
GO
-- newer Records are now updated to current date
SELECT * FROM MyTestTable
GO